SQL Throughput and Backlog | Dataford Interview Questions

Write a SQL query to calculate daily throughput, average cycle time, and backlog by shop location from production tables.

Use the provided production job and shop location data. Treat throughput as jobs completed on a date, cycle time as the elapsed time from job start to completion, and backlog as jobs started but not completed by the end of that date.

Output

One row per shop location and reporting date.
Columns: location_name, report_date, daily_throughput, average_cycle_time, and backlog.
Include locations with no jobs, and order by location name then reporting date.

Schema

shop_locations

Column	Type	Description
shop_idPK	INT	Unique production shop identifier
location_name	VARCHAR(100)	Shop location name

production_jobs

Column	Type	Description
job_idPK	INT	Unique production job identifier
shop_id	INT	Shop assigned to the job
started_at	TIMESTAMP	Timestamp when production started
completed_at	TIMESTAMP	Timestamp when production completed

Your question is SQL Throughput and Backlog. Start with the requirements and the two 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