support_tickets (ticket_id, team, priority, opened_at, first_response_at, resolved_at, reopened, csat) holds support tickets; the timestamps are NULL until the event happens. The report runs at 2024-06-30 12:00:00.
The response SLA is 1 hour for P1 tickets and 8 hours for every other priority, measured from opened_at to first_response_at; a response at exactly the limit meets it.
Build one row per team:
- •
tickets — all its tickets - •
sla_met_pct — of the tickets that have had a first response, the percentage that met the SLA (NULL when none has) - •
p1_breaches — P1 tickets that breached: answered later than 1 hour, or still unanswered more than 1 hour after opening (at the report time) - •
reopen_pct — of the resolved tickets, the percentage reopened (a missing reopened means not reopened; NULL when nothing is resolved) - •
avg_csat — the average csat of resolved tickets that have a rating (NULL when none has)
Round both percentages to 1 decimal place and avg_csat to 2.
Columns: team, tickets, sla_met_pct, p1_breaches, reopen_pct, avg_csat. Sort by team.