SQL for Support Staffing Gaps | Dataford Interview Questions

Write a SQL query to analyze support workload by day and team, joining ticket, agent, and schedule tables to identify staffing gaps.

Use the provided tables to compare ticket volume with scheduled agents. Include dates and teams that appear only in schedules or only in ticket activity.

Output

One row per day and team, with work_date, team, ticket_count, scheduled_agent_count, workload_per_scheduled_agent, and staffing_gap
staffing_gap is the number of tickets exceeding scheduled agents, with zero when no excess exists
Include unmatched dates or teams, and order by work_date ascending, then team ascending

Schema

tickets

Column	Type	Description
ticket_idPK	INT	Unique support ticket identifier
assigned_agent_id	INT	Agent assigned to the ticket
created_at	DATE	Date the ticket was created

agents

Column	Type	Description
agent_idPK	INT	Unique support agent identifier
full_name	VARCHAR(100)	Agent's full name
team	VARCHAR(50)	Support team assigned to the agent

schedules

Column	Type	Description
schedule_idPK	INT	Unique schedule record identifier
agent_id	INT	Scheduled agent
schedule_date	DATE	Date covered by the schedule
status	VARCHAR(20)	Schedule status, such as scheduled or off

Your question is SQL for Support Staffing Gaps. 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