Civic Grievances Filed per Department — SQL Problem
The municipal corporation's grievance portal routes every complaint to one department.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #74
Problem statement
The municipal corporation's grievance portal routes every complaint to one department. Complaints marked rejected were found to be duplicates or spam and must not be counted.
Return each department that has at least one counted complaint, with the columns department and complaints (the number of counted complaints), ordered by complaints descending, then department ascending.
Tables
Table: grievances
| Column | Type |
|---|---|
| grievance_id | int |
| citizen_name | varchar |
| department | enum(Water Supply, Roads, Sanitation, Electricity, Street Lighting) |
| filed_on | date |
| status | enum(open, in_progress, resolved, rejected) |
Primary key: grievance_id.
One row per complaint filed on the portal.
Examples
Example 1
grievances
| grievance_id | citizen_name | department | filed_on | status |
|---|---|---|---|---|
| 1 | Priya Nair | Water Supply | 2024-07-01 | resolved |
| 2 | Rahul Rao | Roads | 2024-07-02 | open |
| 3 | Sneha Iyer | Water Supply | 2024-07-02 | in_progress |
| 4 | Kabir Khan | Sanitation | 2024-07-03 | rejected |
| 5 | Diya Das | Roads | 2024-07-04 | resolved |
| 6 | Arjun Mehta | Electricity | 2024-07-05 | open |
| 7 | Meera Joshi | Sanitation | 2024-07-05 | rejected |
| 8 | Rohan Bose | Electricity | 2024-07-06 | resolved |
Output
| department | complaints |
|---|---|
| Electricity | 2 |
| Roads | 2 |
| Water Supply | 2 |
How to solve Civic Grievances Filed per Department
Filtering in WHERE happens before grouping, so WHERE status <> 'rejected' removes the spam rows and GROUP BY department then counts what is left. A department whose every complaint was rejected has no rows left and simply produces no group, which is exactly what "at least one counted complaint" asks for.
The alternative counts conditionally instead: every department forms a group, a CASE adds 1 only for non-rejected rows, and HAVING … > 0 removes the departments that would show a zero. Both read the table once.
Equal counts are common in small wards, so the order is complaints DESC and then the department name, which makes the answer fully determined. status is never NULL here; if it could be, status <> 'rejected' would drop NULL rows too, and the CASE version would keep them — a difference worth noticing when the column allows NULLs.
Reference solution (MySQL)
SELECT department, COUNT(*) AS complaints
FROM grievances
WHERE status <> 'rejected'
GROUP BY department
ORDER BY complaints DESC, departmentAnother way
SELECT department, SUM(CASE WHEN status = 'rejected' THEN 0 ELSE 1 END) AS complaints
FROM grievances GROUP BY department
HAVING SUM(CASE WHEN status = 'rejected' THEN 0 ELSE 1 END) > 0
ORDER BY complaints DESC, department← Library Loans Overdue on Audit Day · Concerts Without a Single Sold Ticket →