Autoscout24 SQL Analysis

In this article, I will share with you the various analyzes I made on a data set. To do some SQL analysis, I got the German cars dataset from Kaggle at this link:

https://www.kaggle.com/datasets/ander289386/cars-germany

This dataset provides me with various statistics on many German cars, new or used, from the autoscout24 site. These are generally things like price, brand, model, gear. Although the file is not large, it contains a variety of data, making it possible not only for SQL but also for those who will use matplotlib in Python.

1. The first analysis I did on this data set was Cumulative Price Analysis. My aim with this analysis was to determine the total value of the vehicles offered for sale according to their years.

SELECT DISTINCT Year,
       SUM(Price) OVER (ORDER BY Year) AS CumulativePrice
FROM autoscout24
ORDER BY Year DESC;

1. The second analysis I made will be more functional than the first one: it is the average price of vehicles on the market according to model years. Thus, we can find out which model vehicles have which average price. However, I grouped the averages according to vehicle types. As you know, it may also be useful to compare the average price of a used vehicle with the average price of a new vehicle in the last year when the data was entered.

SELECT Year,
       Type,
       AVG(Price) AS AveragePrice
FROM autoscout24
GROUP BY Year, Type
ORDER BY Year DESC, Type;

1. The purpose of my third analysis was to identify the most expensive and cheapest models of each brand on the market and their prices. In the printout we receive, the models are listed from A to Z and the prices are listed from expensive to cheap. Therefore, you can see the most expensive and cheapest vehicles of each brand in order.

SELECT Year, Make, Model, Price
FROM autoscout24 AS A
WHERE Price IN (SELECT MAX(Price) FROM autoscout24 WHERE Make = A.Make UNION SELECT MIN(Price) FROM autoscout24 WHERE Make = A.Make)
ORDER BY Make ASC, Price DESC;

1. The purpose of the fourth analysis is to find the average price and horsepower of each vehicle model. In this analysis, vehicles are ranked by horsepower.

SELECT Make, Model, AVG(Price) AS AvgPrice, AVG(HP) AS AvgHP
FROM autoscout24
GROUP BY Make, Model
HAVING AVG(HP) > (SELECT AVG(HP) FROM autoscout24)
ORDER BY AvgHP DESC;

For now, here are a few data analyzes I have done as examples with SQL. I will also share my more comprehensive analysis on my page. I hope it was useful.