SQL for Operational KPIs | Dataford Interview Questions

Write a SQL query to calculate daily operational KPIs by team, including volume, average handle time, and SLA attainment, from a table of work items and a table of team assignments. Use created_at as the operational date. Average handle time applies to completed items with timestamps, and SLA attainment is the percentage of completed items finished by sla_due_at.

Output

One row per operational date and team, with kpi_date, team_name, volume, avg_handle_time_minutes, and sla_attainment_pct.
Exclude work items without a team assignment and assignments without a matching work item.
Order by kpi_date, then team_name.

Schema

work_items

Column	Type	Description
work_item_idPK	INT	Unique identifier for the work item
created_at	TIMESTAMP	Timestamp when the work item was created
started_at	TIMESTAMP	Timestamp when work began
completed_at	TIMESTAMP	Timestamp when work was completed
sla_due_at	TIMESTAMP	SLA deadline for completing the work item

team_assignments

Column	Type	Description
assignment_idPK	INT	Unique identifier for the assignment
work_item_id	INT	Identifier of the assigned work item
team_name	VARCHAR(100)	Operational team responsible for the work item

Your question is SQL for Operational KPIs. 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