Grievance Tickets per Ward From Reference Codes

Medium
89.

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

ColumnTypeValues
ticket_refvarchar
categoryvarchar
statusenumnew, assigned, closed

Primary key: ticket_ref.

One row per helpline ticket; ticket_ref is unique.

Example

The rows must come back in the order shown.

helpline_tickets

ticket_refcategorystatus
BBMP/W045/2024/0012Garbageclosed
BBMP/W045/2024/0013Potholeassigned
BBMP/W112/2024/0001Streetlightnew
BBMP/W045/2023/0907Garbageclosed
BBMP/W112/2024/0002Water leakclosed
BBMP/W007/2024/0044Stray dogsassigned
BBMP/W007/2025/0001Potholenew

Output

ward_codeticketsstill_open
W04521
W11221
W00711
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.

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

ColumnType
ticket_refvarchar
categoryvarchar
statusenum(new, assigned, closed)

Primary key: ticket_ref.

One row per helpline ticket; ticket_ref is unique.

Examples

Example 1

helpline_tickets

ticket_refcategorystatus
BBMP/W045/2024/0012Garbageclosed
BBMP/W045/2024/0013Potholeassigned
BBMP/W112/2024/0001Streetlightnew
BBMP/W045/2023/0907Garbageclosed
BBMP/W112/2024/0002Water leakclosed
BBMP/W007/2024/0044Stray dogsassigned
BBMP/W007/2025/0001Potholenew

Output

ward_codeticketsstill_open
W04521
W11221
W00711

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_code

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