Grievances Resolved Within the Seven-Day SLA — SQL Problem
The municipal charter promises that a complaint is resolved within 7 days of filing: resolved on the filing day or up to 7 days later meets the SLA.
- Difficulty: Medium
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #82
Problem statement
The municipal charter promises that a complaint is resolved within 7 days of filing: resolved on the filing day or up to 7 days later meets the SLA. Complaints still open have resolved_on NULL and are left out of this report.
For each department with at least one resolved complaint, return department, resolved (resolved complaints), within_sla (those resolved within 7 days) and sla_pct = within_sla * 100 / resolved rounded to 2 decimals. Order by sla_pct descending, then department ascending.
Tables
Table: complaints
| Column | Type |
|---|---|
| complaint_id | int |
| department | varchar |
| ward_no | int |
| filed_on | date |
| resolved_on | date |
Primary key: complaint_id.
One row per complaint. resolved_on is NULL while the complaint is open, and never before filed_on.
Examples
Example 1
complaints
| complaint_id | department | ward_no | filed_on | resolved_on |
|---|---|---|---|---|
| 1 | Water Supply | 12 | 2024-09-01 | 2024-09-05 |
| 2 | Water Supply | 14 | 2024-09-02 | 2024-09-09 |
| 3 | Water Supply | 12 | 2024-09-03 | 2024-09-11 |
| 4 | Roads | 7 | 2024-09-01 | 2024-09-20 |
| 5 | Roads | 7 | 2024-09-04 | NULL |
| 6 | Sanitation | 3 | 2024-09-05 | 2024-09-05 |
| 7 | Street Lighting | 9 | 2024-09-06 | NULL |
| 8 | Roads | 8 | 2024-09-07 | 2024-09-10 |
Output
| department | resolved | within_sla | sla_pct |
|---|---|---|---|
| Sanitation | 1 | 1 | 100 |
| Water Supply | 3 | 2 | 66.67 |
| Roads | 2 | 1 | 50 |
How to solve Grievances Resolved Within the Seven-Day SLA
This is conditional aggregation: within each department, one count of all resolved complaints and a second count of only those that met the SLA, computed in the same pass. WHERE resolved_on IS NOT NULL removes open complaints first, so COUNT(*) is the number resolved and departments with nothing resolved (Street Lighting in the example) form no group.
The SLA test is DATEDIFF(resolved_on, filed_on) <= 7 — the boundary matters: a complaint closed exactly a week later (complaint 2) is on time, eight days (complaint 3) is not. resolved_on <= DATE_ADD(filed_on, INTERVAL 7 DAY) is the same test without subtraction.
The percentage is the on-time count times 100 over the resolved count, rounded to two places because MySQL keeps four decimals on division. The alternative lets every complaint into the group, counts resolved_on (COUNT skips NULLs) and the on-time CASE (which yields NULL, and is skipped, for the rest), then drops departments with zero resolved. Both scan the table once.
Reference solution (MySQL)
SELECT department,
COUNT(*) AS resolved,
SUM(CASE WHEN DATEDIFF(resolved_on, filed_on) <= 7 THEN 1 ELSE 0 END) AS within_sla,
ROUND(SUM(CASE WHEN DATEDIFF(resolved_on, filed_on) <= 7 THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) AS sla_pct
FROM complaints
WHERE resolved_on IS NOT NULL
GROUP BY department
ORDER BY sla_pct DESC, departmentAnother way
SELECT department, resolved, within_sla, ROUND(within_sla * 100 / resolved, 2) AS sla_pct
FROM (
SELECT department, COUNT(resolved_on) AS resolved,
COUNT(CASE WHEN resolved_on <= DATE_ADD(filed_on, INTERVAL 7 DAY) THEN 1 END) AS within_sla
FROM complaints GROUP BY department
) t WHERE resolved > 0
ORDER BY sla_pct DESC, department← Library Members Blocked for Unpaid Fines · Constituency Winners From Booth-Level Counts →