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
| Column | Type |
|---|---|
| state | varchar |
| district | varchar |
| txn_year | int |
| txn_quarter | int |
| txn_count | int |
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
| state | district | txn_year | txn_quarter | txn_count |
|---|---|---|---|---|
| Karnataka | Bengaluru Urban | 2024 | 1 | 900 |
| Karnataka | Mysuru | 2024 | 1 | 500 |
| Karnataka | Belagavi | 2024 | 1 | 400 |
| Karnataka | Udupi | 2024 | 1 | 200 |
| Karnataka | Bengaluru Urban | 2024 | 2 | 950 |
| Karnataka | Mysuru | 2024 | 2 | 520 |
| Karnataka | Belagavi | 2024 | 2 | 380 |
| Karnataka | Udupi | 2024 | 2 | 450 |
| Karnataka | Bengaluru Urban | 2024 | 3 | 980 |
| Karnataka | Mysuru | 2024 | 3 | 560 |
| Karnataka | Belagavi | 2024 | 3 | 390 |
| Karnataka | Udupi | 2024 | 3 | 600 |
| Karnataka | Bengaluru Urban | 2024 | 4 | 990 |
| Karnataka | Mysuru | 2024 | 4 | 560 |
| Karnataka | Belagavi | 2024 | 4 | 395 |
| Karnataka | Udupi | 2024 | 4 | 1000 |
| Karnataka | Belagavi | 2023 | 4 | 2000 |
Output
| state | district | q1_rank | q4_rank |
|---|---|---|---|
| Karnataka | Udupi | 4 | 1 |
Example 2
DistrictQuarter
| state | district | txn_year | txn_quarter | txn_count |
|---|---|---|---|---|
| Telangana | Hyderabad | 2024 | 1 | 800 |
| Telangana | Rangareddy | 2024 | 1 | 300 |
| Telangana | Medchal | 2024 | 1 | 300 |
| Telangana | Warangal | 2024 | 1 | 100 |
| Telangana | Hyderabad | 2024 | 2 | 800 |
| Telangana | Rangareddy | 2024 | 2 | 500 |
| Telangana | Warangal | 2024 | 2 | 450 |
| Telangana | Medchal | 2024 | 2 | 200 |
| Telangana | Hyderabad | 2024 | 3 | 800 |
| Telangana | Rangareddy | 2024 | 3 | 450 |
| Telangana | Medchal | 2024 | 3 | 150 |
| Telangana | Hyderabad | 2024 | 4 | 850 |
| Telangana | Rangareddy | 2024 | 4 | 400 |
| Telangana | Warangal | 2024 | 4 | 900 |
| Telangana | Medchal | 2024 | 4 | 120 |
Output
| state | district | q1_rank | q4_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) = 3Another 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.posAnother 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 →