Dispatch Completion SQL Analysis | Dataford Interview Questions

Write a SQL query to compare planned dispatches versus actual completed loads by day and region, and flag regions where completion rate fell below target.

Use the provided tables and include days with planned dispatches but no completed loads, as well as completed loads without plans.

Output

One row per day and region with activity_date, region_name, planned_dispatches, completed_loads, completion_rate, and below_target
Include all activity dates, calculate a zero completion rate when no dispatches were planned, and flag rates below the region target
Order by activity_date, then region_name

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(100)	Region display name
target_completion_rate	NUMERIC(5,4)	Minimum acceptable completed-to-planned load ratio

planned_dispatches

Column	Type	Description
dispatch_idPK	INT	Unique planned dispatch identifier
dispatch_date	DATE	Date on which the dispatch was planned
region_id	INT	Region responsible for the planned dispatch

completed_loads

Column	Type	Description
load_idPK	INT	Unique completed load identifier
completion_date	DATE	Date on which the load was completed
region_id	INT	Region associated with the completed load

Your question is Dispatch Completion SQL Analysis. 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