SQL for AHT and Backlog | Dataford Interview Questions

Write a SQL query to join support tickets, agents, and shifts to calculate average handle time and backlog by team at Clipboard.

Use resolved tickets to calculate handle time in minutes. Count tickets with open or pending status as backlog, and exclude tickets without a matching agent or shift.

Output

One row per team with team, average_handle_time_minutes, and backlog_count.
Include teams with no qualifying tickets, showing zero backlog and a null average.
Round average handle time to two decimals and order by team ascending.

Schema

support_tickets

Column	Type	Description
ticket_idPK	INT	Unique support ticket identifier
agent_id	INT	Assigned support agent
shift_id	INT	Shift associated with the ticket
status	VARCHAR(20)	Current ticket status
opened_at	TIMESTAMP	Timestamp when the ticket was opened
resolved_at	TIMESTAMP	Timestamp when the ticket was resolved

agents

Column	Type	Description
agent_idPK	INT	Unique support agent identifier
agent_name	VARCHAR(100)	Support agent name
team	VARCHAR(50)	Clipboard support team

shifts

Column	Type	Description
shift_idPK	INT	Unique shift identifier
shift_date	DATE	Date of the shift
starts_at	TIMESTAMP	Shift start timestamp
ends_at	TIMESTAMP	Shift end timestamp

Your question is SQL for AHT and Backlog. 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