SQL for Supplier Lead Time YoY | Dataford Interview Questions

Write a SQL query to join these three tables and calculate the year-over-year growth for supplier lead times. Compute the annual average lead time for each supplier and material. Return NULL for growth when no prior year exists or the prior-year average is zero.

Output

One row per supplier, material, and year with available lead-time records
Columns: supplier_id, supplier_name, material_id, material_name, calendar_year, annual_avg_lead_time, and yoy_growth_pct
Order by supplier_id, material_id, and calendar_year ascending

Schema

suppliers

Column	Type	Description
supplier_idPK	INT	Unique supplier identifier
supplier_name	VARCHAR(100)	Supplier name
supplier_region	VARCHAR(50)	Supplier operating region

materials

Column	Type	Description
material_idPK	INT	Unique material identifier
material_name	VARCHAR(100)	Material name
material_category	VARCHAR(50)	Material category

supplier_lead_times

Column	Type	Description
lead_time_idPK	INT	Unique lead-time observation identifier
supplier_id	INT	Supplier associated with the observation
material_id	INT	Material associated with the observation
calendar_year	INT	Calendar year of the observation
lead_time_days	DECIMAL(10,2)	Observed supplier lead time in days

Your question is SQL for Supplier Lead Time YoY. Start with the requirements and the three tables in the Question tab.

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

Problem