Grievance Tickets per Ward From Reference Codes
The corporation's helpline stores no ward column; the ward is encoded in the ticket reference, which always has the form BBMP/<ward>/<year>/<serial> — for…
- Difficulty: Medium
- Topics: Strings, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #89
Problem statement
The corporation's helpline stores no ward column; the ward is encoded in the ticket reference, which always has the form BBMP/<ward>/<year>/<serial> — for example BBMP/W045/2024/0012, where the ward code is always a W and three digits and the year four digits.
For tickets of the year 2024 only, return ward_code (such as W045), tickets (the number of tickets) and still_open (tickets whose status is not closed), ordered by tickets descending, then ward_code ascending.
Tables
Table: helpline_tickets
| Column | Type |
|---|---|
| ticket_ref | varchar |
| category | varchar |
| status | enum(new, assigned, closed) |
Primary key: ticket_ref.
One row per helpline ticket; ticket_ref is unique.
Examples
Example 1
helpline_tickets
| ticket_ref | category | status |
|---|---|---|
| BBMP/W045/2024/0012 | Garbage | closed |
| BBMP/W045/2024/0013 | Pothole | assigned |
| BBMP/W112/2024/0001 | Streetlight | new |
| BBMP/W045/2023/0907 | Garbage | closed |
| BBMP/W112/2024/0002 | Water leak | closed |
| BBMP/W007/2024/0044 | Stray dogs | assigned |
| BBMP/W007/2025/0001 | Pothole | new |
Output
| ward_code | tickets | still_open |
|---|---|---|
| W045 | 2 | 1 |
| W112 | 2 | 1 |
| W007 | 1 | 1 |
How to solve Grievance Tickets per Ward From Reference Codes
When a fact lives inside a string, the query has to parse it out before it can group by it. The reference has /-separated fields, and the nested SUBSTRING_INDEX idiom picks field k: SUBSTRING_INDEX(ref, '/', k) keeps everything before the *k*-th slash, and SUBSTRING_INDEX(…, '/', -1) then keeps the last field of that prefix. Field 2 is the ward and field 3 the year.
The year filter is on the parsed year, so the 2023 and 2025 tickets of ward W045 and W007 in the example are excluded. Then a group per ward counts all tickets and, conditionally, the ones not yet closed.
Since the format is fixed-width (BBMP/ is five characters, the ward four), SUBSTRING(ticket_ref, 6, 4) and a LIKE 'BBMP/____/2024/%' pattern (each _ matches one character) are a cheaper alternative. Storing the ward and year as columns would be the real fix — parsing on every query defeats any index.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ticket_ref, '/', 2), '/', -1) AS ward_code,
COUNT(*) AS tickets,
SUM(CASE WHEN status <> 'closed' THEN 1 ELSE 0 END) AS still_open
FROM helpline_tickets
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(ticket_ref, '/', 3), '/', -1) = '2024'
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(ticket_ref, '/', 2), '/', -1)
ORDER BY tickets DESC, ward_codeAnother way
SELECT SUBSTRING(ticket_ref, 6, 4) AS ward_code, COUNT(*) AS tickets,
COUNT(CASE WHEN status IN ('new', 'assigned') THEN 1 END) AS still_open
FROM helpline_tickets
WHERE ticket_ref LIKE 'BBMP/____/2024/%'
GROUP BY SUBSTRING(ticket_ref, 6, 4)
ORDER BY tickets DESC, ward_code← Monthly Library Issues by Genre · Change Between a Donor's First and Latest Gift →