logo

9 General Motors SQL Interview Questions (Updated 2024)

Updated on

June 30, 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!

General Motors SQL Interview Questions

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_idsale_datesale_pricemodel
1012022-04-1035000Cruze
1022022-04-1537000Cruze
1032022-05-0235500Cruze
1042022-05-0336000Cruze
1052022-04-0460000Silverado
1062022-05-1063000Silverado
1072022-05-1261000Silverado
Example Output:
monthmodelaverage_pricechange
4Cruze36000null
5Cruze35750-250
4Silverado60000null
5Silverado620002000

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: Amazon Business Intelligence SQL 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_idnamesalarydepartment_id
1Emma Thompson38001
2Daniel Rodriguez22301
3Olivia Smith20001
4Noah Johnson68002
5Sophia Martinez17501
8William Davis68002
10James Anderson40001

Example Input:

department_iddepartment_name
1Data Analytics
2Data Science

Example Output:

department_namenamesalary
Data AnalyticsJames Anderson4000
Data AnalyticsEmma Thompson3800
Data AnalyticsDaniel Rodriguez2230
Data ScienceNoah Johnson6800
Data ScienceWilliam Davis6800

Try this interview question directly within the browser on DataLemur:

Top 3 Department Salaries

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:


General Motors SQL Interview Questions

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_idvehicle_idcustomer_idsale_dateprice
11003002019-06-0830000
22004002019-06-1040000
31005002020-06-1830000
Example Input:
model_idmodel_namemodel_year
100Chevrolet Bolt2019
200Buick Encore2019

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_idcar_modelrecord_datemileage
1Corsa06/01/2022 00:00:0023000
2Astra06/03/2022 00:00:0045000
3Cascada06/05/2022 00:00:0020000
4Corsa07/08/2022 00:00:0024000
5Astra07/08/2022 00:00:0046000

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_modelavg_mileage
Corsa23500
Astra45500
Cascada20000

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_idcar_idsale_datesale_price
11012016-05-0725000
21022016-06-0132000
31032017-04-1018000
41012017-07-0926000
51022017-08-0531000
Example Input:
car_idmodel_namelaunch_year
101Chevrolet2005
102Buick2008
103GMC2010

Expected Output:

yearmodel_nameavg_sale_price
2016Chevrolet25000
2016Buick32000
2017GMC18000
2017Chevrolet26000
2017Buick31000

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_idcar_namebrand
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_idcar_namebrand
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. DataLemur SQL and Data Science Interview Questions

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.

DataLemur SQL tutorial

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:

General Motors Data Scientist

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

Ace the Data Science Interview Book on Amazon