9 General Motors SQL Interview Questions (Updated 2024)
General Motors employees write SQL queries daily to analyze automotive performance data, which helps in improving customer experience and vehicle performance. So, it shouldn't surprise you that General Motors almost always asks SQL query questions in interviews for Data Science, Analytics, and & Data Engineering jobs.
To help you ace the General Motors SQL interview, here’s 9 General Motors SQL interview questions – scroll down to start solving them!
9 General Motors SQL Interview Questions
SQL Question 1: Vehicle Sales Analysis
Given a table 'vehicle_sales' with vehicle ID, sale date, and sale price, write a SQL query that calculates the increase or decrease in the average sale price of each model from one month to the next.
Example Input:
| vehicle_id | sale_date | sale_price | model |
|---|---|---|---|
| 101 | 2022-04-10 | 35000 | Cruze |
| 102 | 2022-04-15 | 37000 | Cruze |
| 103 | 2022-05-02 | 35500 | Cruze |
| 104 | 2022-05-03 | 36000 | Cruze |
| 105 | 2022-04-04 | 60000 | Silverado |
| 106 | 2022-05-10 | 63000 | Silverado |
| 107 | 2022-05-12 | 61000 | Silverado |
Example Output:
| month | model | average_price | change |
|---|---|---|---|
| 4 | Cruze | 36000 | null |
| 5 | Cruze | 35750 | -250 |
| 4 | Silverado | 60000 | null |
| 5 | Silverado | 62000 | 2000 |
Answer:
The SQL query for this question would be:
This query first calculates the average sale price for each model for each month. Then, for each row, it calculates the difference in the average sale price from the previous month's average sale price using the LAG window function. Thus, it gives the monthly average sale price of each model and its change (increase or decrease) from the previous month.
To practice another window function question on DataLemur's free interactive SQL code editor, try this Amazon BI Engineer interview question:
SQL Question 2: Top 3 Department Salaries
Given a table of General Motors employee salary data, write a SQL query to find the top 3 highest paid employees within each department.
General Motors Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Try this interview question directly within the browser on DataLemur:
Answer:
We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.
If the solution above is confusing, you can find a detailed solution here: Top 3 Department Salaries.
SQL Question 3: What do the / operators do, and can you give an example?
The / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.
Note that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle (but don't worry about knowing which DBMS supports which exact commands since General Motors interviewers aren't trying to trip you up on memorizing SQL syntax).
For a tangible example of in PostgreSQL, suppose you were doing an HR Analytics project for General Motors, and had access to General Motors's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables.
You could use operator to find all contractors who never were a employee using this query:
SQL Question 4: Vehicle Sales Data Analysis
Suppose General Motors would like to analyze their vehicle sales data. They have two tables: , containing sales transactions with , , , and as columns; and , containing vehicle model details with , and as columns.
The in the table corresponds to the in the table.
Write a SQL query to find out the sales breakdown by year and model: the total number of vehicles sold and the total sales amount, grouped by and .
Example Input:
| sale_id | vehicle_id | customer_id | sale_date | price |
|---|---|---|---|---|
| 1 | 100 | 300 | 2019-06-08 | 30000 |
| 2 | 200 | 400 | 2019-06-10 | 40000 |
| 3 | 100 | 500 | 2020-06-18 | 30000 |
Example Input:
| model_id | model_name | model_year |
|---|---|---|
| 100 | Chevrolet Bolt | 2019 |
| 200 | Buick Encore | 2019 |
Answer:
This query joins the table with table on and . Then it groups the result by and and provides count of (total vehicles sold) and sum of (total sales amount) for each group.
SQL Question 5: What's the SQL command do, and when would you use it?
The SQL command merges the results of multiple statements and keeps only those rows that are present in all sets.
For example, say you were doing an HR Analytics project for General Motors, and had access to General Motors's employees and contractors data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all contractors who also show up in the employees table:
SQL Question 6: Average Mileage of General Motors Car Models
You are a data analyst at General Motors and you have been tasked to find the average mileage of every car model in the fleet for the year 2022. Mileage is a relevant metric for both the company and customers as it helps in assessing the overall performance and efficiency of the vehicles.
The information on mileage for each car for the different instances it was recorded is stored in a table named . The data structure of is as follows:
Example Input:
| record_id | car_model | record_date | mileage |
|---|---|---|---|
| 1 | Corsa | 06/01/2022 00:00:00 | 23000 |
| 2 | Astra | 06/03/2022 00:00:00 | 45000 |
| 3 | Cascada | 06/05/2022 00:00:00 | 20000 |
| 4 | Corsa | 07/08/2022 00:00:00 | 24000 |
| 5 | Astra | 07/08/2022 00:00:00 | 46000 |
Answer:
To analyze the average mileage for each model, you can group by the column and calculate the average for each group.
In PostgreSQL, you can solve this problem with the following query:
This would give an output table with each unique car model, along with its average mileage, for the year 2022.
Example Output:
| car_model | avg_mileage |
|---|---|
| Corsa | 23500 |
| Astra | 45500 |
| Cascada | 20000 |
The output table shows the average mileage of each car model for the year 2022. The AVG function computes the average of the grouped mileage values.
To practice a very similar question try this interactive Amazon Server Utilization Time Question which is similar for calculating summary statistics over a fleet or this Alibaba Compressed Mean Question which is similar for Calculating averages for a business metric.
SQL Question 7: Can you explain the distinction between a correlated and a non-correlated sub-query?
A non-correlated sub-query is not linked to the outer query and can be run on its own. It does not reference any columns from the outer query and is used to retrieve additional data for the outer query. On the other hand, a correlated sub-query is one that relies on the outer query and cannot be run independently. It uses the outer query to filter or transform data by referencing a column from it, while the outer query uses the results of the inner query.
Here is an example of a non-correlated sub-query:
The sub-query in this case is non-correlated, as it does not reference any columns from the outer query.
Here is an example of a correlated sub-query:
This query selects the and total sales of all General Motors customers in the sales table whose are greater than the average of their own customer group. The sub-query in this case is correlated with the outer query, as it references the column from the outer query ().
Non-correlated sub-queries are typically faster, as they only have to be executed once, wheareas correlated sub-queries are slower since they have to be re-evaluated for each row of the outer query.
Read about how GM (General Motors) uses AI to help power innovation!
SQL Question 8: Average Sales of Car Models
As an analyst at General Motors, you are tasked with figuring out the average sales across different car models for each year. Data is captured in two tables, and . The table captures the sales with columns , , and while table captures the model details with columns , and .
Example Input:
| sale_id | car_id | sale_date | sale_price |
|---|---|---|---|
| 1 | 101 | 2016-05-07 | 25000 |
| 2 | 102 | 2016-06-01 | 32000 |
| 3 | 103 | 2017-04-10 | 18000 |
| 4 | 101 | 2017-07-09 | 26000 |
| 5 | 102 | 2017-08-05 | 31000 |
Example Input:
| car_id | model_name | launch_year |
|---|---|---|
| 101 | Chevrolet | 2005 |
| 102 | Buick | 2008 |
| 103 | GMC | 2010 |
Expected Output:
| year | model_name | avg_sale_price |
|---|---|---|
| 2016 | Chevrolet | 25000 |
| 2016 | Buick | 32000 |
| 2017 | GMC | 18000 |
| 2017 | Chevrolet | 26000 |
| 2017 | Buick | 31000 |
Answer:
This SQL query joins the two tables and on . Then it groups by the extracted year from and and finally calculates average . The query also orders the result by year and model name. This gives us the average sales across different car models for each year.
SQL Question 9: Find the cars manufactured by "Chevrolet" sub-brand
As a database administrator at General Motors, you are tasked with fetching the details of all cars manufactured under the sub-brand "Chevrolet". The car's name contains the string "Chevy" in it. Your task is to write a SQL command to fetch these records.
Provide examples in a table:
Example Input:
| car_id | car_name | brand |
|---|---|---|
| 1001 | 'Chevy Camaro' | 'General Motors' |
| 1002 | 'Buick Encore' | 'General Motors' |
| 1003 | 'Chevy Impala' | 'General Motors' |
| 1004 | 'Cadillac Escalade' | 'General Motors' |
| 1005 | 'Chevy Corvette' | 'General Motors' |
You are to retrieve records where like .
Answer:
This will produce:
Example Output:
| car_id | car_name | brand |
|---|---|---|
| 1001 | 'Chevy Camaro' | 'General Motors' |
| 1003 | 'Chevy Impala' | 'General Motors' |
| 1005 | 'Chevy Corvette' | 'General Motors' |
This SQL command allows you to filter and fetch only the records where the starts with the string 'Chevy', which are the cars manufactured by the sub-brand Chevrolet.
Preparing For The General Motors SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the General Motors SQL interview is to solve as many practice SQL interview questions as you can!
In addition to solving the above General Motors SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like FAANG tech companies and tech startups.
Each DataLemur SQL question has hints to guide you, detailed solutions and crucially, there is an interactive SQL code editor so you can right online code up your query and have it executed.
To prep for the General Motors SQL interview you can also be useful to solve SQL problems from other automotive companies like:
However, if your SQL query skills are weak, don't worry about jumping right into solving questions – go learn SQL with this free SQL for Data Analytics course.
This tutorial covers SQL topics like GROUP BY and math functions in SQL – both of these come up frequently in General Motors SQL interviews.
General Motors Data Science Interview Tips
What Do General Motors Data Science Interviews Cover?
In addition to SQL query questions, the other types of questions tested in the General Motors Data Science Interview are:
- Statistics & AB Testing Questions
- Python Pandas or R Coding Questions
- Analytics and Product-Metrics Questions
- ML Modelling Questions
- Behavioral Interview Questions
How To Prepare for General Motors Data Science Interviews?
To prepare for General Motors Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from FAANG (FB, Apple, Amazon, Netflix, Google)
- a crash course covering Product Analytics, SQL & ML
- over 1000+ reviews on Amazon & 4.5-star rating