SQL KPI Trends With YoY | Dataford Interview Questions

Your question is SQL KPI Trends With YoY. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Write a SQL query to calculate monthly KPI trends with year-over-year comparison for Volvo Group.

Use completed deliveries for valid Volvo Group vehicle models sold through active dealer locations. Include months with no prior-year comparison, and calculate delivery volume, revenue, revenue per vehicle, and year-over-year percentage changes.

Output

One row per calendar month with columns month_start, delivered_units, total_revenue, revenue_per_vehicle, prior_year_units, prior_year_revenue, units_yoy_pct, and revenue_yoy_pct.
Include all qualifying months, sort chronologically, and return NULL percentage values when no prior-year baseline exists or the baseline is zero.

Schema

vehicle_deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery identifier
model_id	INT	Vehicle model reference
dealer_id	INT	Dealer location reference
delivery_date	DATE	Date of delivery
status	VARCHAR(20)	Delivery status
net_revenue	NUMERIC(14,2)	Net revenue recognized for the delivery

vehicle_models

Column	Type	Description
model_idPK	INT	Unique vehicle model identifier
model_name	VARCHAR(80)	Vehicle model name
vehicle_type	VARCHAR(40)	Vehicle category
brand	VARCHAR(60)	Vehicle brand

dealer_locations

Column	Type	Description
dealer_idPK	INT	Unique dealer identifier
dealer_name	VARCHAR(100)	Dealer name
country	VARCHAR(50)	Dealer country
region	VARCHAR(50)	Commercial region
active	BOOLEAN	Whether the dealer is currently active