SQL for Monthly SLA Attainment | Dataford Interview Questions

Write a SQL query to calculate monthly SLA attainment by team, including total tickets, on-time resolution rate, and month-over-month change.

Use the provided ticket and team data. Treat a ticket as on time only when it has a resolution timestamp at or before its SLA due timestamp. Tickets without a resolution timestamp are not on time.

Output

One row per team and ticket-creation month, excluding tickets without a matching team.
Columns: team_name, month, total_tickets, on_time_resolution_rate, and month_over_month_change.
Rates and changes must be percentages rounded to two decimals. The first month for each team should have a NULL change. Order by team name, then month.

Schema

teams

Column	Type	Description
team_idPK	INT	Unique team identifier
team_name	VARCHAR(100)	Team name

tickets

Column	Type	Description
ticket_idPK	INT	Unique ticket identifier
team_id	INT	Assigned team identifier
created_at	TIMESTAMP	Ticket creation timestamp
sla_due_at	TIMESTAMP	SLA deadline timestamp
resolved_at	TIMESTAMP	Ticket resolution timestamp

Your question is SQL for Monthly SLA Attainment. 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