Monthly Ticket Refund Report by Railway Zone — SQL Medium Problem

Problem statement

Passengers who cancel a train ticket file a refund request, which ends up refunded, rejected or still pending. Requests filed online without choosing a zone have zone NULL.

For every month and zone with at least one request, return these columns:

• month — the month the request was filed, as text 'YYYY-MM';
• zone;
• requests — the number of requests;
• refunded — how many of them were refunded;
• requested_amount — the total amount asked for;
• refunded_amount — the total amount of the refunded requests, 0 (never NULL) when none was refunded.

Requests with a NULL zone form their own group in each month, shown with zone NULL. Return the rows in any order.

Tables

Table: RefundRequest

Primary key: request_id.

One row per refund request; amount is in rupees and zone is NULL for requests filed without one.

Examples

Example 1

RefundRequest

Output

How to solve Monthly Ticket Refund Report by Railway Zone

Each output row is one (month, zone) pair, so the query groups by both. The month comes from the date: DATE_FORMAT(filed_on, '%Y-%m') gives text like 2025-05; LEFT(filed_on, 7) or CONCAT(YEAR(…), '-', LPAD(MONTH(…), 2, '0')) produce the same. Group by the very expression you select (or its alias): MySQL's ONLY_FULL_GROUP_BY refuses a selected expression it cannot match to the grouping. Grouping by MONTH(filed_on) alone would merge May 2025 with May of any other year.

The four figures come from one pass over each group. COUNT(*) and SUM(amount) cover all requests. For the refunded ones, conditional aggregation keeps a single scan: SUM(IF(status = 'refunded', 1, 0)) counts them and SUM(IF(status = 'refunded', amount, 0)) adds up only their amounts. Writing it with CASE WHEN … THEN amount END and no ELSE gives NULL — not 0 — for a group with no refunds, because SUM over nothing but NULLs is NULL; the statement asks for 0, hence the COALESCE.

The NULL zone needs no special handling. GROUP BY treats NULLs as equal for grouping, so each month's zone-less requests form one group, shown with zone NULL. Adding WHERE zone IS NOT NULL would lose them.

The cost is one scan plus the grouping. Running several separate queries — one for totals, one for refunds — and joining them would read the table repeatedly and need outer joins to keep months with no refunds.

Reference solution (MySQL)

SELECT DATE_FORMAT(filed_on, '%Y-%m') AS month,
       zone,
       COUNT(*) AS requests,
       SUM(IF(status = 'refunded', 1, 0)) AS refunded,
       SUM(amount) AS requested_amount,
       SUM(IF(status = 'refunded', amount, 0)) AS refunded_amount
FROM RefundRequest
GROUP BY DATE_FORMAT(filed_on, '%Y-%m'), zone

Another way

SELECT LEFT(filed_on, 7) AS month, zone, COUNT(*) AS requests,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded,
  SUM(amount) AS requested_amount,
  COALESCE(SUM(CASE WHEN status = 'refunded' THEN amount END), 0) AS refunded_amount
FROM RefundRequest
GROUP BY LEFT(filed_on, 7), zone

Another way

SELECT CONCAT(YEAR(filed_on), '-', LPAD(MONTH(filed_on), 2, '0')) AS month, zone, COUNT(*) AS requests,
  SUM(status = 'refunded') AS refunded, SUM(amount) AS requested_amount,
  SUM((status = 'refunded') * amount) AS refunded_amount
FROM RefundRequest
GROUP BY month, zone