SQL Rolling 4-Week Regional Performance | Dataford Interview Questions

Your question is SQL Rolling 4-Week Regional Performance. Start with the requirements and the two 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 analyze weekly operational performance by region at Medzed, joining orders and staffing tables and calculating a rolling 4-week average. Include completed orders and staffing hours for every region-week represented in either table. Treat weeks as starting on Monday and avoid division by zero.

Output

One row per region and week, with region, week_start, completed_orders, staffed_hours, orders_per_staff_hour, and rolling_4_week_avg.
Include unmatched region-weeks, showing zero counts or hours where appropriate and NULL performance when staffing hours are zero.
Sort by region, then week_start ascending.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
region	VARCHAR(50)	Medzed operating region
order_date	DATE	Date the order was created
status	VARCHAR(20)	Order lifecycle status

staffing

Column	Type	Description
staffing_idPK	INT	Unique staffing record identifier
region	VARCHAR(50)	Medzed operating region
staffing_date	DATE	Date staffing was recorded
staffed_hours	DECIMAL(10,2)	Available staff hours