Goodyear Tire & Rubber employees use SQL daily for extracting tire performance data and analyzing sales trends. That's why Goodyear Tire & Rubber often tests SQL problems during interviews for Data Analytics, Data Science, and Data Engineering jobs.
Thus, to help you practice, here’s 8 Goodyear Tire & Rubber SQL interview questions – how many can you solve?
As an analyst at Goodyear Tire & Rubber, you are required to perform a monthly review of products based on customer feedback. For this task, you are provided with a reviews
dataset. The dataset has the review_id
, user_id
, submit_date
, product_id
and stars
(rating) columns.
Write a SQL query to compute the average product ratings for each product on a monthly basis. Order the results by the month (in ascending order) and then by average rating (in descending order).
reviews
Example Input:review_id | user_id | submit_date | product_id | stars |
---|---|---|---|---|
6171 | 123 | 2022-06-08 | 1 | 4 |
7802 | 265 | 2022-06-10 | 2 | 4 |
5293 | 362 | 2022-06-18 | 1 | 3 |
6352 | 192 | 2022-07-26 | 2 | 3 |
4517 | 981 | 2022-07-05 | 2 | 2 |
SELECT EXTRACT(MONTH FROM submit_date) AS mth, product_id AS product, ROUND(AVG(stars)::numeric, 2) AS avg_stars FROM reviews GROUP BY mth, product ORDER BY mth ASC, avg_stars DESC;
With this query, we are extracting the month part from the submit_date
column, grouping the data by product_id
and month
, and then calculating the average rating for each group. ROUND
function is used to limit the precision of average rating to two decimal points. The resulting dataset is then ordered by month in ascending order and by average rating in descending order, thereby helping us understand which products did well in which month based on customer ratings.
mth | product | avg_stars |
---|---|---|
6 | 1 | 3.50 |
6 | 2 | 4.00 |
7 | 2 | 2.50 |
Pro Tip: Window functions are a popular SQL interview topic, so practice all the window function problems on DataLemur
Imagine you had a table of Goodyear Tire & Rubber employee salaries, along with which department they were in. Write a query to compare the average salary of employees in each department to the company's average salary. Return the comparison result as 'higher', 'lower', or 'same' for each department. Display the department ID, payment month (in MM-YYYY format), and the comparison.
Code your solution to this interview question interactively on DataLemur:
The answer is LONG – 30+ lines of SQL. You can find a step-by-step solution here: Department Salaries.
Normalization is the process of organizing fields and tables of a database to minimize redundancy and dependency. While there are technically 5 levels (normal forms), the 3 most important normal forms you need to know about for SQL interviews at Goodyear Tire & Rubber are:
Given two tables sales
and tires
, write an SQL query to find the total sales (quantity) for each type of tire for the year 2022.
The sales
table has the following structure:
##### `sales` Example Input: |**sales_id**|**tire_id**|**date_of_sale**|**quantity**| |:----|:----|:----|:----| |1001|201|01/01/2022|50| |1002|202|01/02/2022|30| |1003|203|02/10/2022|20| |1004|201|03/07/2022|40| |1005|202|04/22/2022|70|
And The tires
table has the following structure:
##### `tires` Example Input: |**tire_id**|**tire_type**| |:----|:----| |201|All-Terrain| |202|Performance| |203|Winter|
SELECT t.tire_type, SUM(s.quantity) as total_sales FROM sales s INNER JOIN tires t ON s.tire_id = t.tire_id WHERE EXTRACT(YEAR FROM s.date_of_sale) = 2022 GROUP BY t.tire_type;
This query joins the sales
and tires
tables on the tire_id
field. It then filters the sales records for the year 2022. It finally calculates, for each tire type, the sum of the sales quantity - giving total sales for 2022 for each type of tire.
Example output would be:
##### Example Output: |**tire_type**|**total_sales**| |:----|:----| |All-Terrain|90| |Performance|100| |Winter|20|
This indicates that in 2022, the company sold 90 All-Terrain tires, 100 Performance tires, and 20 Winter tires.
INTERSECT
do?Similar to the UNION
and EXCEPT
/MINUS
operators, the PostgreSQL INTERSECT operator combines result sets of two or more SELECT
statements into a single result set. However, INTERSECT
only returns the rows that are in BOTH select statements.
For a concrete example, say you were on the Sales Analytics team at Goodyear Tire & Rubber, and had data on sales leads exported from both HubSpot and Salesforce CRMs in two different tables. To write a query to analyze leads created after 2023 started, that show up in both CRMs, you would use the INTERSECT
command:
SELECT email, job_title, company_id FROM goodyear_tire_rubber_hubspot_leads WHERE created_at > '2023-01-01' INTERSECT SELECT email, job_title, company_id FROM goodyear_tire_rubber_sfdc_leads WHERE created_at > '2023-01-01';
Learn more about Goodyear and read their sustainibility progress report!
As a data analyst at Goodyear Tire & Rubber, you are tasked to find all customers who have purchased tires with names that contain "All-Season". Provide both the customer IDs and the names of the tires that fit this description.
##### `purchases` Example Input:
|**purchase_id**|**customer_id**|**purchase_date**|**tire_id**|
|:----|:----|:----|:----|
|7091|3456|03/12/2022 00:00:00|2501|
|5521|7890|04/14/2022 00:00:00|3205|
|8296|4571|06/18/2022 00:00:00|2501|
|9852|1112|08/26/2022 00:00:00|3205|
|5437|9001|09/05/2022 00:00:00|2501|
##### `tires` Example Input:
|**tire_id**|**tire_name**|
|:----|:----|
|2501|Ultra Grip Ice All-Season|
|3205|Eagle Sport All-Season|
|3607|Assurance Fuel Max|
##### Example Output:
|**customer_id**|**tire_name**|
|:----|:----|
|3456|Ultra Grip Ice All-Season|
|7890|Eagle Sport All-Season|
|4571|Ultra Grip Ice All-Season|
|1112|Eagle Sport All-Season|
|9001|Ultra Grip Ice All-Season|
#### Answer:
SELECT purchases.customer_id, tires.tire_name FROM purchases INNER JOIN tires ON purchases.tire_id = tires.tire_id WHERE tires.tire_name LIKE '%All-Season%'
This query first joins the purchases
and tires
tables on the condition that their tire_id
column values are the same. Then it applies the WHERE
clause, filtering for tire names that contain the substring "All-Season". The output will be the customer_id
from the purchases
table and the tire_name
from the tires
table, where the tire name contains "All-Season".
An inner join only includes rows from both tables that satisfy the join condition, whereas a full outer join includes all rows from both tables, regardless of whether they match the join condition or not.
To demonstrate each kind, Imagine you were working on a Advertising Analytics project at Goodyear Tire & Rubber and had two database tables: an Advertising_Campaigns
table that contains data on Google Ads keywords and their bid amounts, and a Sales
table with information on product sales and the Google Ads keywords that drove those sales.
An INNER JOIN
retrieves rows from both tables where there is a match in the shared key or keys. For example, an INNER JOIN between the Advertising_Campaigns
table and the Sales
table could be performed using the keyword column as the shared key. This would retrieve only the rows where the keyword in the Advertising_Campaigns
table matches the keyword in the Sales
table.
A FULL OUTER JOIN
retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL
values will be returned for the columns of the non-matching table.
Goodyear Tire & Rubber would like to give a discount on their tire products. They have a bulk purchase strategy where if a customer buys more than 10 tires, they will receive a discount. The discount rate is determined by the remainder of the tire quantity divided by 3 (using MOD()). If the remainder is 0, the discount is 30%; if the remainder is 1, the discount is 10%; and if the remainder is 2, the discount is 20%.
The question requires you to write a SQL statement that calculates the final tire price after the discount has been applied for each transaction. You need to round the final prices to 2 decimal points using the ROUND() function.
A sample table for the transactions is provided below:
transactions
Example Input:transaction_id | tire_type | unit_price | quantity |
---|---|---|---|
9823 | All-Season Tires | 100.50 | 15 |
4562 | Performance Tires | 200.75 | 12 |
6479 | Winter Tires | 150.25 | 23 |
8984 | All-Terrain Tires | 170.50 | 11 |
2625 | Fuel-Efficient Tires | 120.95 | 19 |
SELECT transaction_id, tire_type, unit_price, quantity, ROUND( unit_price * quantity * ( CASE WHEN MOD(quantity, 3) = 0 THEN 0.7 -- 30% Discount WHEN MOD(quantity, 3) = 1 THEN 0.9 -- 10% Discount WHEN MOD(quantity, 3) = 2 THEN 0.8 -- 20% Discount END ), 2) AS Final_Price FROM transactions WHERE quantity > 10;
In this SQL statement, we first check whether the quantity of tires purchased is more than 10. For these records, we calculate the final price by first determining the discount rate using the MOD function with 3 as the divisor. The CASE WHEN statement will return the appropriate multiplier for the discount based on the remainder of the division. Finally, we use the ROUND function to round off the final price to 2 decimal points.
To practice a very similar question try this interactive Stripe Repeated Payments Question which is similar for applying conditions to calculate numbers or this Facebook Advertiser Status Question which is similar for <Using CASE/WHEN for specific determinations.
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. In addition to solving the earlier Goodyear Tire & Rubber SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Google, Microsoft and Silicon Valley startups.
Each SQL question has hints to guide you, step-by-step solutions and most importantly, there's an interactive SQL code editor so you can right online code up your SQL query answer and have it executed.
To prep for the Goodyear Tire & Rubber SQL interview you can also be a great idea to practice SQL problems from other automotive companies like:
But if your SQL skills are weak, forget about going right into solving questions – go learn SQL with this free SQL for Data Analytics course.
This tutorial covers topics including handling strings and creating pairs via SELF-JOINs – both of which come up often in Goodyear Tire & Rubber interviews.
Besides SQL interview questions, the other types of questions covered in the Goodyear Tire & Rubber Data Science Interview include:
I'm a tad biased, but I think the optimal way to prep for Goodyear Tire & Rubber Data Science interviews is to read the book I wrote: Ace the Data Science Interview.
The book has 201 data interview questions taken from Facebook, Google, & Amazon. It also has a crash course covering Product Analytics, SQL & ML. And finally it's helped thousands of people land their dream job in data, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.