UPI Payments by Month and City — SQL Medium Problem

Problem statement

A payments app wants a monthly report of its UPI transactions per city. For every month and city that has at least one transaction, return:

• month — the month as text in the form 'YYYY-MM';
• city;
• txn_count — the number of transactions;
• success_count — how many of them succeeded;
• txn_amount — the total amount of all of them;
• success_amount — the total amount of the successful ones (0 if none succeeded).

Transactions whose city is unknown (NULL) are grouped together as one city, shown as NULL. The same month of different years is a different month. Return the rows in any order.

Tables

Table: UpiTransaction

Primary key: txn_id.

amount is in rupees. city is where the payer was, or NULL when the app could not tell.

Examples

Example 1

UpiTransaction

Output

How to solve UPI Payments by Month and City

Two grouping keys: the month and the city. The month comes from the date with DATE_FORMAT(txn_date, '%Y-%m'), which turns 2025-01-09 into '2025-01'. That keeps the year in the key, so December 2024 and December 2025 stay apart; grouping by MONTH(txn_date) alone would merge them.

Within each (month, city) group the four numbers are plain or conditional aggregates:

• COUNT(*) and SUM(amount) cover all of the group's transactions;
• SUM(IF(status = 'success', 1, 0)) counts only the successes, and SUM(IF(status = 'success', amount, 0)) adds only their amounts — giving 0, not NULL, for a group with no successful transaction.

NULL cities need no special code: GROUP BY puts every NULL into one group, which is what the statement asks for. (A WHERE city = … filter, by contrast, could never match them.)

Other spellings: LEFT(txn_date, 7) gives the same 'YYYY-MM' text from a date; grouping by YEAR(txn_date), MONTH(txn_date) and building the label with CONCAT and LPAD works too; and SUM(status = 'success') counts with a comparison that is 1 or 0, so CASE WHEN … THEN amount ELSE 0 END and (status = 'success') * amount are interchangeable with IF. Every version is one scan plus a grouping step.

Reference solution (MySQL)

SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month,
       city,
       COUNT(*) AS txn_count,
       SUM(IF(status = 'success', 1, 0)) AS success_count,
       SUM(amount) AS txn_amount,
       SUM(IF(status = 'success', amount, 0)) AS success_amount
FROM UpiTransaction
GROUP BY DATE_FORMAT(txn_date, '%Y-%m'), city

Another way

SELECT LEFT(txn_date, 7) AS month, city, COUNT(*) AS txn_count,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
       SUM(amount) AS txn_amount,
       SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS success_amount
FROM UpiTransaction
GROUP BY LEFT(txn_date, 7), city

Another way

SELECT CONCAT(YEAR(txn_date), '-', LPAD(MONTH(txn_date), 2, '0')) AS month, city, COUNT(*) AS txn_count,
       SUM(status = 'success') AS success_count, SUM(amount) AS txn_amount,
       SUM((status = 'success') * amount) AS success_amount
FROM UpiTransaction
GROUP BY month, city