Blog

DROP TABLE IF EXISTS carsales;CREATE TABLE carsales (car_id…

DROP TABLE IF EXISTS carsales;CREATE TABLE carsales (car_id int PRIMARY KEY,  year int ,  fueltype text,mileage int, condition text, price int , model text);INSERT INTO carsales VALUES(33,2022, ‘Hybrid’ ,50812, ‘Used’ ,92010, ‘Corolla’),(310,2022, ‘Petrol’ ,211284, ‘Like New’ ,5472, ‘Prius’),(358,2020, ‘Diesel’ ,260705, ‘Used’ ,47415, ‘RAV4’),(627,2022, ‘Diesel’ ,4791, ‘Like New’ ,43350, ‘Camry’),(680,2021, ‘Electric’ ,163041, ‘Used’ ,70017, ‘Prius’),(701,2023, ‘Hybrid’ ,294153, ‘Used’ ,93901, ‘Corolla’),(765,2020, ‘Diesel’ ,162475, ‘Used’ ,58064, ‘Camry’),(869,2023, ‘Petrol’ ,26325, ‘Like New’ ,23849, ‘RAV4’),(900,2023, ‘Hybrid’ ,140689, ‘Like New’ ,90139, ‘Corolla’),(913,2023, ‘Hybrid’ ,224647, ‘Like New’ ,44340, ‘Corolla’),(1148,2022, ‘Diesel’ ,95967, ‘Like New’ ,40263, ‘Camry’),(1447,2021, ‘Electric’ ,13919, ‘Used’ ,14728, ‘Camry’),(1551,2020, ‘Electric’ ,67664, ‘Like New’ ,54949, ‘Prius’),(1835,2022, ‘Hybrid’ ,286425, ‘Used’ ,57205, ‘Camry’),(1886,2021, ‘Hybrid’ ,109268, ‘Used’ ,99400, ‘Camry’),(1931,2021, ‘Petrol’ ,20605, ‘Like New’ ,76664, ‘Corolla’),(1967,2022, ‘Petrol’ ,257716, ‘Like New’ ,55114, ‘Camry’),(2058,2021, ‘Hybrid’ ,164340, ‘Like New’ ,47374, ‘RAV4’),(2198,2020, ‘Hybrid’ ,23466, ‘Like New’ ,73872, ‘Corolla’),(2248,2023, ‘Hybrid’ ,128131, ‘Like New’ ,35412, ‘RAV4’),(2255,2021, ‘Hybrid’ ,123447, ‘Used’ ,41756, ‘RAV4’),(2321,2023, ‘Petrol’ ,45313, ‘Used’ ,70376, ‘Prius’),(2427,2023, ‘Electric’ ,148940, ‘Used’ ,89390, ‘RAV4’); Source: https://www.kaggle.com/datasets/mexwell/gym-check-ins-and-user-metadataLinks to an external site. The dataset contains resale information for four models from a single car brand that were available on the market in 2024. Each record includes the vehicle’s manufacture year, fuel type, condition, and used mileage. Suppose you’re trying to determine which used car offers the best value based on its price and mileage.   Start by executing the provided script in pgAdmin to initialize your dataset. Then, construct a SQL query using a Common Table Expression structure, organized into three logical stages: Part 1: Aggregation by Model, Engine Type, and Year First extract the last two digits of the manufacture year and alias this column as yy.  Then compute a custom metric called score: score = (mileage/10000) * (price/1000), this metric increases when either mileage or price is high, helping identify less favorable resale options.  Then calculate the average of score at the (model, fueltype, yy) level. In the output CTE, retain only the following columns: model, fueltype, yy, and score.   Part 2: Moving and Overall Averages Create a second CTE based on the temporal output from Part 1 to compute two key metrics: 2.1: Year-over-Year Comparison To evaluate score changes across manufacturing years, use the LAG function to compare each item’s score with the most recent prior entry of the same kind.  For each (model, fueltype) group, calculate the difference between the current item’s score and the latest available previous entry’s score—regardless of whether the years are consecutive. Alias this column as diff: diff = current_item_score – previous_item_score   For example, the diff value for Corolla (Hybrid) manufactured in 2023 reflects the score difference between the 2023 and 2022 entries of the same kind, which is 1207.93 2.2: Overall Score Average Within the same CTE, compute the overall average score for each (model, fueltype) across all records, and alias this column as avg_m_ft.   Note: Both 2.1 and 2.2 must be computed within the same CTE.   Part 3: Final Output Return the following columns in the final result:  model, fueltype, yy, score, diff, avg_m_ft Finally, filter the results to include only rows with a manufacturing year of 2022 or later. The final output should match the structure and layout of the output shown below, allowing for minor differences due to rounding. model fueltype yy score diff avg_m_ft Camry Diesel 22 203.58 -739.81 573.49 Camry Hybrid 22 1638.49 552.37 1362.31 Camry Petrol 22 1420.38         [null] 1420.38 Corolla Hybrid 22 467.52 294.17 772.11 Corolla Hybrid 23 1675.46 1207.93 772.11 Prius Petrol 22 115.61         [null] 217.25 Prius Petrol 23 318.89 203.28 217.25 RAV4 Electric 23 1331.37         [null] 1331.37 RAV4 Hybrid 23 453.74 -193.27 550.37 RAV4 Petrol 23 62.78         [null] 62.78   Submit your complete query in the window below. You must use the template below to build your query: — Use common table expression to write the query in three partsWITH  first  AS (–Part 1 ),second  AS (–Part 2) — Part 3SELECT  Submit your complete query in the window below.

DROP TABLE IF EXISTS carsales;CREATE TABLE carsales (car_id…

DROP TABLE IF EXISTS carsales;CREATE TABLE carsales (car_id int PRIMARY KEY,  year int ,  fueltype text,mileage int, condition text, price int , model text);INSERT INTO carsales VALUES(33,2022, ‘Hybrid’ ,50812, ‘Used’ ,92010, ‘Corolla’),(310,2022, ‘Petrol’ ,211284, ‘Like New’ ,5472, ‘Prius’),(358,2020, ‘Diesel’ ,260705, ‘Used’ ,47415, ‘RAV4’),(627,2022, ‘Diesel’ ,4791, ‘Like New’ ,43350, ‘Camry’),(680,2021, ‘Electric’ ,163041, ‘Used’ ,70017, ‘Prius’),(701,2023, ‘Hybrid’ ,294153, ‘Used’ ,93901, ‘Corolla’),(765,2020, ‘Diesel’ ,162475, ‘Used’ ,58064, ‘Camry’),(869,2023, ‘Petrol’ ,26325, ‘Like New’ ,23849, ‘RAV4’),(900,2023, ‘Hybrid’ ,140689, ‘Like New’ ,90139, ‘Corolla’),(913,2023, ‘Hybrid’ ,224647, ‘Like New’ ,44340, ‘Corolla’),(1148,2022, ‘Diesel’ ,95967, ‘Like New’ ,40263, ‘Camry’),(1447,2021, ‘Electric’ ,13919, ‘Used’ ,14728, ‘Camry’),(1551,2020, ‘Electric’ ,67664, ‘Like New’ ,54949, ‘Prius’),(1835,2022, ‘Hybrid’ ,286425, ‘Used’ ,57205, ‘Camry’),(1886,2021, ‘Hybrid’ ,109268, ‘Used’ ,99400, ‘Camry’),(1931,2021, ‘Petrol’ ,20605, ‘Like New’ ,76664, ‘Corolla’),(1967,2022, ‘Petrol’ ,257716, ‘Like New’ ,55114, ‘Camry’),(2058,2021, ‘Hybrid’ ,164340, ‘Like New’ ,47374, ‘RAV4’),(2198,2020, ‘Hybrid’ ,23466, ‘Like New’ ,73872, ‘Corolla’),(2248,2023, ‘Hybrid’ ,128131, ‘Like New’ ,35412, ‘RAV4’),(2255,2021, ‘Hybrid’ ,123447, ‘Used’ ,41756, ‘RAV4’),(2321,2023, ‘Petrol’ ,45313, ‘Used’ ,70376, ‘Prius’),(2427,2023, ‘Electric’ ,148940, ‘Used’ ,89390, ‘RAV4’); Source: https://www.kaggle.com/datasets/mexwell/gym-check-ins-and-user-metadataLinks to an external site. The dataset contains resale information for four models from a single car brand that were available on the market in 2024. Each record includes the vehicle’s manufacture year, fuel type, condition, and used mileage. Suppose you’re trying to determine which used car offers the best value based on its price and mileage.   Start by executing the provided script in pgAdmin to initialize your dataset. Then, construct a SQL query using a Common Table Expression structure, organized into three logical stages: Part 1: Aggregation by Model, Engine Type, and Year First extract the last two digits of the manufacture year and alias this column as yy.  Then compute a custom metric called score: score = (mileage/10000) * (price/1000), this metric increases when either mileage or price is high, helping identify less favorable resale options.  Then calculate the average of score at the (model, fueltype, yy) level. In the output CTE, retain only the following columns: model, fueltype, yy, and score.   Part 2: Moving and Overall Averages Create a second CTE based on the temporal output from Part 1 to compute two key metrics: 2.1: Year-over-Year Comparison To evaluate score changes across manufacturing years, use the LAG function to compare each item’s score with the most recent prior entry of the same kind.  For each (model, fueltype) group, calculate the difference between the current item’s score and the latest available previous entry’s score—regardless of whether the years are consecutive. Alias this column as diff: diff = current_item_score – previous_item_score   For example, the diff value for Corolla (Hybrid) manufactured in 2023 reflects the score difference between the 2023 and 2022 entries of the same kind, which is 1207.93 2.2: Overall Score Average Within the same CTE, compute the overall average score for each (model, fueltype) across all records, and alias this column as avg_m_ft.   Note: Both 2.1 and 2.2 must be computed within the same CTE.   Part 3: Final Output Return the following columns in the final result:  model, fueltype, yy, score, diff, avg_m_ft Finally, filter the results to include only rows with a manufacturing year of 2022 or later. The final output should match the structure and layout of the output shown below, allowing for minor differences due to rounding. model fueltype yy score diff avg_m_ft Camry Diesel 22 203.58 -739.81 573.49 Camry Hybrid 22 1638.49 552.37 1362.31 Camry Petrol 22 1420.38         [null] 1420.38 Corolla Hybrid 22 467.52 294.17 772.11 Corolla Hybrid 23 1675.46 1207.93 772.11 Prius Petrol 22 115.61         [null] 217.25 Prius Petrol 23 318.89 203.28 217.25 RAV4 Electric 23 1331.37         [null] 1331.37 RAV4 Hybrid 23 453.74 -193.27 550.37 RAV4 Petrol 23 62.78         [null] 62.78   Submit your complete query in the window below. You must use the template below to build your query: — Use common table expression to write the query in three partsWITH  first  AS (–Part 1 ),second  AS (–Part 2) — Part 3SELECT  Submit your complete query in the window below.