Find the dealerships that beat their 2018 units target. For…
Find the dealerships that beat their 2018 units target. For each, show the actual units sold in 2018, the units_target, and the variance (actual minus target), highest variance first.Hint: build 2018 actual units per dealership in a CTE (a COUNT of sales, dealership_id cast to int), join it to dealership_targets on dealership_id and target_year = 2018, then keep only the dealerships whose actual units exceed the target. This is the toughest problem here — chain it in named steps and test each one on its own before assembling.
Read DetailsFor each product_type, return the top 3 products by total re…
For each product_type, return the top 3 products by total revenue. Show product_type, model, revenue, and the within-type rank.Hint: build per-product revenue in a CTE, then RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC), and keep rank
Read DetailsFor each sale, show the sales_amount alongside the average s…
For each sale, show the sales_amount alongside the average sales_amount for that product’s product_type, so each row can be compared to its type’s average. Do not collapse the rows.Hint: AVG(…) OVER (PARTITION BY product_type).
Read Details