PhonePe: Top District by Transaction Count in Each State, Q2 2025

Medium
883.

PhonePe: Top District by Transaction Count in Each State, Q2 2025

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

ColumnType
statevarchar
districtvarchar
txn_datedate
txn_countint

Primary key (together): state, district, txn_date.

One row per district per day that had transactions; txn_count is in thousands.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

DistrictDaily

statedistricttxn_datetxn_count
KarnatakaBengaluru Urban2025-04-01900
KarnatakaBengaluru Urban2025-06-30850
KarnatakaMysuru2025-05-10300
KarnatakaMysuru2025-07-015000
TelanganaHyderabad2025-04-15700
TelanganaRangareddy2025-05-15400
TelanganaRangareddy2025-06-15300
MaharashtraPune2025-03-312000

Output

statedistrictq2_txns
KarnatakaBengaluru Urban1750
TelanganaHyderabad700
TelanganaRangareddy700
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.

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

ColumnType
statevarchar
districtvarchar
txn_datedate
txn_countint

Primary key: state, district, txn_date.

One row per district per day that had transactions; txn_count is in thousands.

Examples

Example 1

DistrictDaily

statedistricttxn_datetxn_count
KarnatakaBengaluru Urban2025-04-01900
KarnatakaBengaluru Urban2025-06-30850
KarnatakaMysuru2025-05-10300
KarnatakaMysuru2025-07-015000
TelanganaHyderabad2025-04-15700
TelanganaRangareddy2025-05-15400
TelanganaRangareddy2025-06-15300
MaharashtraPune2025-03-312000

Output

statedistrictq2_txns
KarnatakaBengaluru Urban1750
TelanganaHyderabad700
TelanganaRangareddy700

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

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