12|SQL – New Management, New Focus

[DylanKing]

BYU Student Author: @DylanKing
Reviewers: @Boston, @TylerBooth, @Alex_Garrett
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

• SQL
• Go here to perform this challenge in Alteryx

Need a program? Click here.

Overview
You are the new owner of a used car lot. The previous management was buying models from any and every manufacturer you can think of: Audi, BMW, Saturn, Volvo, etc. As the new owner, you hope to shift your focus to acquiring used cars from manufacturers whose models have the least amount of average depreciation on their car models. You imagine that doing so will prevent you from taking big losses by holding onto cars for long periods of time.

The data provided gives you access to information on manufacturers, models, sales in thousands of units, the resale price, the price in thousands of dollars, and more from 2008-2012. With the data provided, use SQL to find the top 10 manufacturers with the lowest average depreciation across their various models. Follow the instructions below.

Instructions
Import the data into a database program like Microsoft Access. Be sure to write code that follows the given criteria for your output:

1. Calculate Depreciation for each specific model. Convert the number from thousands to regular units (i.e. 5.14 in thousands to 5140)
2. Remove any rows of data that have a null price or resale value.
3. Average the depreciation for each model across the manufacturers. Round to two decimal places.
4. Remove any rows of data for manufacturers who did not have at least 3 models to prevent outliers.
5. Sort by average depreciation in ascending order. The output should only include the manufacturers and their average depreciation. Limit the output to the ten manufacturers with the least average depreciation. Your two column headers should be Manufacturer and Average_Depreciation.

Data Files

• Challenge12_Car_Model_Sales_2008-2012.xlsx
• Challenge12_Data_Dictionary.docx

Suggestions and Hints

• Depreciation = Price - Resale Value
• When using SQL, if you want to limit your output to the top 10, one way to achieve this is by writing TOP 10 in the select statement in front of manufacturer. For more information check out this website: SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM

Solution

Solution Image

Solution Code

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000) IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2);

Challenge12_Solution.accdb
Solution Video: Challenge 12|SQL – New Management, New Focus

[Benjamin_Lau]

Here is my SQL solution. I added a part to make sure each manufacturer has 3 distinct models. It looks like most of them have at least 3 models. Also, it is kind of frustrating that Access doesn’t support Count Distinct function.

SELECT TOP 10 car.Manufacturer AS Manufacturer, ROUND(AVG(((car.Price_in_thousands) - (car.year_resale_value))* 1000), 2) AS Average_Depreciation

FROM Car_sales AS car INNER JOIN
(SELECT COUNT (*) AS NumOfModels , car.Manufacturer
FROM (SELECT DISTINCT car.Model, car.Manufacturer FROM Car_Sales AS car)
GROUP BY car.Manufacturer) AS counting
ON car.Manufacturer = counting.Manufacturer

WHERE car.Price_in_thousands IS NOT NULL AND car.year_resale_value IS NOT NULL AND counting.NumofModels > 2
GROUP BY car.Manufacturer
HAVING COUNT(car.ID) > 2
ORDER BY ROUND(AVG((car.Price_in_thousands - car.year_resale_value)* 1000), 2)
;

[ameliacurtis]

Here is my SQL solution! I enjoyed figuring out how to enter a depreciation formula into my query.

[baylesma]

Here is my SQL for it!

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) as Average_Depreciation
FROM Car_sales
WHERE Price_in_thousands IS NOT NULL AND year_resale_value IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID) > 2
ORDER BY ROUND(AVG((Price_in_thousands) - (year_resale_value)*1000), 2)
;

[Colton_Bullock]

Here is my attempt at this problem!

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000) IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2);

[Colton_Bullock]

Great solution benjamin! i can tell you understand SQL

[Lydia_Karlson]

Here’s my solution:

[Brad_Jones]

Great practice and application! The ROUND formula was a good challenge to figure out.

[Kelty_Dagley]

Here is my SQL code for the challenge. I enjoyed learning how to do depreciation with SQL and helped me get more practice with SQL!

[Maclean_Lunt]

Here is my solution! It was difficult for me at first figuring out how to calculate depreciation, but I learned the value of nested functions in the SELECT statement. I also did not realize the SELECT function had an option such as SELECT TOP 10! Thank you for this practice.

[Madison_Jones]

Here’s the code I ended up with! Loved this challenge!

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands) - (year_resale_value))*1000),2) AS AVGDepreciation
FROM Car_sales
WHERE Price_in_thousands IS NOT NULL AND year_resale_value IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT (ID) >2
ORDER BY ROUND(AVG(((Price_in_thousands) - (year_resale_value))*1000),2) ;

[Zach_Dullum]

Great review of SQL and the HAVING COUNT clause.

[Zach_Dullum]

Similarly, I just learned about the SELECT TOP 10 clause. Really interesting stuff!

[Andrew_Wilson]

Here is my code for this problem!

I ran into a few problems trying to make an aggregate average function. Because of this I had to separate each of my average functions. Although it took a few more words of code to solve I was able to get it to return the same values.

I appreciated the challenge and the broader use of the data extraction functions.

[Nick_S_Mangum]

Here is my solution!

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands) - (year_resale_value))*1000),2) AS Average_Depreciation
FROM Table2
WHERE Price_in_thousands IS NOT NULL AND year_resale_value IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID) > 2
ORDER BY ROUND(AVG(((Price_in_thousands) - (year_resale_value))*1000),2);

[Nick_S_Mangum]

Great solution, Zach!

[jason_ng]

Here is my solution
SELECT TOP 10 Manufacturer, Round( AVG((Price_in_thousands -[__year_resale_value])*1000),2) as Average_Depreciation
From Car_sales
Where ((Price_in_thousands -[__year_resale_value])*1000) IS NOT NULL
Group by Manufacturer
Having Count(ID)>2
Order by Round( AVG((Price_in_thousands -[__year_resale_value])*1000),2) DESC;

[Jaxon_Jones]

Here it is! I thought that the prompt was really interesting. Challenging and a lot to learn.
SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) as Average_Depreciation
FROM Car_sales
WHERE Price_in_thousands IS NOT NULL AND year_resale_value IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID) > 2
ORDER BY ROUND(AVG((Price_in_thousands) - (year_resale_value)*1000), 2)

[Abigail_Armstrong]

Fun challenge!

[Matthew_Layden]

Here’s what I got. I really liked the challenge