PhonePe: Top District by Transaction Count in Each State, Q2 2025
A Pulse-style dashboard publishes daily UPI transaction counts per district.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #883
Problem statement
A Pulse-style dashboard publishes daily UPI transaction counts per district. For the quarterly write-up, the data team names the busiest district of every state for April–June 2025.
For each state, return state, district and q2_txns — the district's total txn_count from 2025-04-01 to 2025-06-30 inclusive — for the district with the highest total. If districts tie for the highest, return each of them. States with no data in that quarter do not appear. Return the rows in any order.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: DistrictDaily
| Column | Type |
|---|---|
| state | varchar |
| district | varchar |
| txn_date | date |
| txn_count | int |
Primary key: state, district, txn_date.
One row per district per day that had transactions; txn_count is in thousands.
Examples
Example 1
DistrictDaily
| state | district | txn_date | txn_count |
|---|---|---|---|
| Karnataka | Bengaluru Urban | 2025-04-01 | 900 |
| Karnataka | Bengaluru Urban | 2025-06-30 | 850 |
| Karnataka | Mysuru | 2025-05-10 | 300 |
| Karnataka | Mysuru | 2025-07-01 | 5000 |
| Telangana | Hyderabad | 2025-04-15 | 700 |
| Telangana | Rangareddy | 2025-05-15 | 400 |
| Telangana | Rangareddy | 2025-06-15 | 300 |
| Maharashtra | Pune | 2025-03-31 | 2000 |
Output
| state | district | q2_txns |
|---|---|---|
| Karnataka | Bengaluru Urban | 1750 |
| Telangana | Hyderabad | 700 |
| Telangana | Rangareddy | 700 |
How to solve PhonePe: Top District by Transaction Count in Each State, Q2 2025
Two steps. First collapse the daily rows to one total per district for the quarter: filter txn_date BETWEEN '2025-04-01' AND '2025-06-30' (inclusive at both ends, which is the quarter exactly) and SUM(txn_count) grouped by state and district. A big day just outside the quarter, like Mysuru's 1 July in the example, must not count.
Second, pick the top total in each state. RANK() over PARTITION BY state ORDER BY q2_txns DESC gives every district that shares the maximum the position 1, so ties are kept as the statement asks. A state with no rows inside the quarter has no totals and disappears on its own.
The alternatives do the comparison differently: NOT EXISTS keeps a district when no district of the same state has a larger total, and MAX(…) OVER (PARTITION BY state) puts the state's best total on each row to compare against. The aggregate is one scan; the comparison runs over the much smaller set of district totals.
Reference solution (MySQL)
WITH q2 AS (
SELECT state, district, SUM(txn_count) AS q2_txns
FROM DistrictDaily
WHERE txn_date BETWEEN '2025-04-01' AND '2025-06-30'
GROUP BY state, district
), ranked AS (
SELECT state, district, q2_txns,
RANK() OVER (PARTITION BY state ORDER BY q2_txns DESC) AS pos
FROM q2
)
SELECT state, district, q2_txns FROM ranked WHERE pos = 1Another way
WITH q2 AS (SELECT state, district, SUM(txn_count) AS q2_txns FROM DistrictDaily WHERE txn_date >= '2025-04-01' AND txn_date < '2025-07-01' GROUP BY state, district)
SELECT a.state, a.district, a.q2_txns FROM q2 a
WHERE NOT EXISTS (SELECT 1 FROM q2 b WHERE b.state = a.state AND b.q2_txns > a.q2_txns)Another way
WITH q2 AS (SELECT state, district, SUM(txn_count) AS q2_txns FROM DistrictDaily WHERE QUARTER(txn_date) = 2 AND YEAR(txn_date) = 2025 GROUP BY state, district)
SELECT state, district, q2_txns FROM (SELECT q2.*, MAX(q2_txns) OVER (PARTITION BY state) AS top FROM q2) t WHERE q2_txns = top← PhonePe: Monthly P2P Versus P2M Payment Value in 2024 · PhonePe: Users Whose First Successful Payment Was at a Merchant →