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.
Output details
- Sort by
kpi_date ascending, then team_name ascending.
- Round
avg_handle_time_minutes and sla_attainment_pct to two decimal places.
- Calculate average handle time only when both
started_at and completed_at are non-NULL; return NULL when no qualifying items exist.
- Calculate SLA attainment among completed items only, treat
completed_at <= sla_due_at as attained, and return NULL when no items are completed.
- Retain every matching assignment row without deduplication, so duplicate assignments contribute separately to the KPIs.
