Grievances Resolved Within the Seven-Day SLA

Medium
82.

Grievances Resolved Within the Seven-Day SLA

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

ColumnType
complaint_idint
departmentvarchar
ward_noint
filed_ondate
resolved_ondate

Primary key: complaint_id.

One row per complaint. resolved_on is NULL while the complaint is open, and never before filed_on.

Example

The rows must come back in the order shown.

complaints

complaint_iddepartmentward_nofiled_onresolved_on
1Water Supply122024-09-012024-09-05
2Water Supply142024-09-022024-09-09
3Water Supply122024-09-032024-09-11
4Roads72024-09-012024-09-20
5Roads72024-09-04NULL
6Sanitation32024-09-052024-09-05
7Street Lighting92024-09-06NULL
8Roads82024-09-072024-09-10

Output

departmentresolvedwithin_slasla_pct
Sanitation11100
Water Supply3266.67
Roads2150
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
complaint_idint
departmentvarchar
ward_noint
filed_ondate
resolved_ondate

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_iddepartmentward_nofiled_onresolved_on
1Water Supply122024-09-012024-09-05
2Water Supply142024-09-022024-09-09
3Water Supply122024-09-032024-09-11
4Roads72024-09-012024-09-20
5Roads72024-09-04NULL
6Sanitation32024-09-052024-09-05
7Street Lighting92024-09-06NULL
8Roads82024-09-072024-09-10

Output

departmentresolvedwithin_slasla_pct
Sanitation11100
Water Supply3266.67
Roads2150

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, department

Another 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 →