PhonePe: Districts Whose State Rank Rose Every Quarter of 2024

Hard
895.

PhonePe: Districts Whose State Rank Rose Every Quarter of 2024

Pulse-style quarterly data ranks districts within their state by transaction count. For a story on fast-growing towns, the data team wants the districts that climbed every quarter of 2024.

In each state and each quarter of 2024, rank the districts by txn_count, highest first; tied districts share a rank and the next rank is skipped (1, 2, 2, 4). Return state, district, q1_rank and q4_rank for every district that has a row in all four quarters of 2024 and whose rank number got strictly smaller from Q1 to Q2, from Q2 to Q3 and from Q3 to Q4. Only districts with a row in a quarter are ranked in it. 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: DistrictQuarter

ColumnType
statevarchar
districtvarchar
txn_yearint
txn_quarterint
txn_countint

Primary key (together): state, district, txn_year, txn_quarter.

One row per district per quarter with transactions; txn_count is in thousands.

Examples

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

Example 1

DistrictQuarter

statedistricttxn_yeartxn_quartertxn_count
KarnatakaBengaluru Urban20241900
KarnatakaMysuru20241500
KarnatakaBelagavi20241400
KarnatakaUdupi20241200
KarnatakaBengaluru Urban20242950
KarnatakaMysuru20242520
KarnatakaBelagavi20242380
KarnatakaUdupi20242450
KarnatakaBengaluru Urban20243980
KarnatakaMysuru20243560
7 more rows

Output

statedistrictq1_rankq4_rank
KarnatakaUdupi41

Example 2

DistrictQuarter

statedistricttxn_yeartxn_quartertxn_count
TelanganaHyderabad20241800
TelanganaRangareddy20241300
TelanganaMedchal20241300
TelanganaWarangal20241100
TelanganaHyderabad20242800
TelanganaRangareddy20242500
TelanganaWarangal20242450
TelanganaMedchal20242200
TelanganaHyderabad20243800
TelanganaRangareddy20243450
5 more rows

Output

statedistrictq1_rankq4_rank
No rows
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: Districts Whose State Rank Rose Every Quarter of 2024

Pulse-style quarterly data ranks districts within their state by transaction count.

  • Difficulty: Hard
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #895

Problem statement

Pulse-style quarterly data ranks districts within their state by transaction count. For a story on fast-growing towns, the data team wants the districts that climbed every quarter of 2024.

In each state and each quarter of 2024, rank the districts by txn_count, highest first; tied districts share a rank and the next rank is skipped (1, 2, 2, 4). Return state, district, q1_rank and q4_rank for every district that has a row in all four quarters of 2024 and whose rank number got strictly smaller from Q1 to Q2, from Q2 to Q3 and from Q3 to Q4. Only districts with a row in a quarter are ranked in it. 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: DistrictQuarter

ColumnType
statevarchar
districtvarchar
txn_yearint
txn_quarterint
txn_countint

Primary key: state, district, txn_year, txn_quarter.

One row per district per quarter with transactions; txn_count is in thousands.

Examples

Example 1

DistrictQuarter

statedistricttxn_yeartxn_quartertxn_count
KarnatakaBengaluru Urban20241900
KarnatakaMysuru20241500
KarnatakaBelagavi20241400
KarnatakaUdupi20241200
KarnatakaBengaluru Urban20242950
KarnatakaMysuru20242520
KarnatakaBelagavi20242380
KarnatakaUdupi20242450
KarnatakaBengaluru Urban20243980
KarnatakaMysuru20243560
KarnatakaBelagavi20243390
KarnatakaUdupi20243600
KarnatakaBengaluru Urban20244990
KarnatakaMysuru20244560
KarnatakaBelagavi20244395
KarnatakaUdupi202441000
KarnatakaBelagavi202342000

Output

statedistrictq1_rankq4_rank
KarnatakaUdupi41

Example 2

DistrictQuarter

statedistricttxn_yeartxn_quartertxn_count
TelanganaHyderabad20241800
TelanganaRangareddy20241300
TelanganaMedchal20241300
TelanganaWarangal20241100
TelanganaHyderabad20242800
TelanganaRangareddy20242500
TelanganaWarangal20242450
TelanganaMedchal20242200
TelanganaHyderabad20243800
TelanganaRangareddy20243450
TelanganaMedchal20243150
TelanganaHyderabad20244850
TelanganaRangareddy20244400
TelanganaWarangal20244900
TelanganaMedchal20244120

Output

statedistrictq1_rankq4_rank

How to solve PhonePe: Districts Whose State Rank Rose Every Quarter of 2024

Two window passes. The first is the ranking itself: RANK() OVER (PARTITION BY state, txn_quarter ORDER BY txn_count DESC) over the 2024 rows, which gives tied districts the same rank and skips the next number, as the statement defines. Filtering the year before ranking matters — the big 2023 Belagavi row in the first example would otherwise take first place in the Q4 table.

The second pass looks back one quarter per district with LAG(pos). After grouping by district, COUNT(*) = 4 demands a row in every quarter, and three strict improvements (pos < prev_pos) out of the three comparisons means the rank fell every time. Udupi goes 4 → 3 → 2 → 1 and qualifies. In the second example Warangal would have climbed but has no Q3 row, so it is out; Rangareddy's tie for second in Q1 then a plain second in Q2 is no improvement.

The alternatives pivot instead of using LAG: a four-way self-join of the ranked rows (each join also enforces presence in that quarter), or a rank computed as 1 + the number of districts with a larger count — the same skipping-ranks definition — followed by conditional MAX per quarter. A NULL quarter makes their comparisons unknown, which drops the district just like the count test.

Reference solution (MySQL)

WITH ranked AS (
  SELECT state, district, txn_quarter,
         RANK() OVER (PARTITION BY state, txn_quarter ORDER BY txn_count DESC) AS pos
  FROM DistrictQuarter
  WHERE txn_year = 2024
), steps AS (
  SELECT state, district, txn_quarter, pos,
         LAG(pos) OVER (PARTITION BY state, district ORDER BY txn_quarter) AS prev_pos
  FROM ranked
)
SELECT state, district,
       MAX(CASE WHEN txn_quarter = 1 THEN pos END) AS q1_rank,
       MAX(CASE WHEN txn_quarter = 4 THEN pos END) AS q4_rank
FROM steps
GROUP BY state, district
HAVING COUNT(*) = 4
   AND SUM(CASE WHEN pos < prev_pos THEN 1 ELSE 0 END) = 3

Another way

WITH ranked AS (SELECT state, district, txn_quarter, RANK() OVER (PARTITION BY state, txn_quarter ORDER BY txn_count DESC) AS pos FROM DistrictQuarter WHERE txn_year = 2024)
SELECT r1.state, r1.district, r1.pos AS q1_rank, r4.pos AS q4_rank
FROM ranked r1
JOIN ranked r2 ON r2.state = r1.state AND r2.district = r1.district AND r2.txn_quarter = 2
JOIN ranked r3 ON r3.state = r1.state AND r3.district = r1.district AND r3.txn_quarter = 3
JOIN ranked r4 ON r4.state = r1.state AND r4.district = r1.district AND r4.txn_quarter = 4
WHERE r1.txn_quarter = 1 AND r2.pos < r1.pos AND r3.pos < r2.pos AND r4.pos < r3.pos

Another way

WITH ranked AS (
  SELECT d.state, d.district, d.txn_quarter,
         1 + (SELECT COUNT(*) FROM DistrictQuarter o WHERE o.state = d.state AND o.txn_year = 2024 AND o.txn_quarter = d.txn_quarter AND o.txn_count > d.txn_count) AS pos
  FROM DistrictQuarter d WHERE d.txn_year = 2024
), pivot AS (
  SELECT state, district,
         MAX(CASE WHEN txn_quarter = 1 THEN pos END) AS q1, MAX(CASE WHEN txn_quarter = 2 THEN pos END) AS q2,
         MAX(CASE WHEN txn_quarter = 3 THEN pos END) AS q3, MAX(CASE WHEN txn_quarter = 4 THEN pos END) AS q4
  FROM ranked GROUP BY state, district
)
SELECT state, district, q1 AS q1_rank, q4 AS q4_rank FROM pivot WHERE q2 < q1 AND q3 < q2 AND q4 < q3

← PhonePe: Merchant-Spend Quartiles of Users in Each City, March 2025 · PhonePe: Merchants Earning Half Their Takings From Three Customers →