PhonePe: Merchant-Spend Quartiles of Users in Each City, March 2025
The rewards team sizes city-wise cashback budgets by splitting each city's paying users into four equal-sized spend groups.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #894
Problem statement
The rewards team sizes city-wise cashback budgets by splitting each city's paying users into four equal-sized spend groups. For March 2025, a user's spend is the sum of their successful merchant (P2M) payments; users with no such payment are left out.
In each city, sort the users by spend from highest to lowest, breaking ties by user_id ascending, and deal them into quartiles 1 to 4 the way NTILE(4) does: the groups differ in size by at most one, and the larger groups come first (10 users → 3, 3, 2, 2). A city with fewer than four users has fewer quartiles. Return city, quartile, users, min_spend, max_spend and total_spend for every quartile, ordered by city, then quartile.
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: AppUser
| Column | Type |
|---|---|
| user_id | int |
| city | varchar |
Primary key: user_id.
One row per user, with the city of their registered address.
Table: P2mPayment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| amount | int |
| status | enum(success, failed) |
| paid_on | date |
Primary key: payment_id.
One row per payment by a user to a merchant; user_id always names a user.
Examples
Example 1
AppUser
| user_id | city |
|---|---|
| 1 | Pune |
| 2 | Pune |
| 3 | Pune |
| 4 | Pune |
| 5 | Pune |
| 6 | Pune |
| 7 | Indore |
| 8 | Indore |
| 9 | Surat |
P2mPayment
| payment_id | user_id | amount | status | paid_on |
|---|---|---|---|---|
| 11 | 1 | 900 | success | 2025-03-02 |
| 12 | 2 | 400 | success | 2025-03-05 |
| 13 | 2 | 300 | success | 2025-03-25 |
| 14 | 3 | 700 | success | 2025-03-09 |
| 15 | 4 | 250 | success | 2025-03-31 |
| 16 | 5 | 250 | success | 2025-03-12 |
| 17 | 6 | 5000 | failed | 2025-03-14 |
| 18 | 6 | 120 | success | 2025-04-01 |
| 19 | 7 | 60 | success | 2025-03-03 |
| 20 | 8 | 80 | success | 2025-03-04 |
| 21 | 9 | 50 | failed | 2025-03-04 |
Output
| city | quartile | users | min_spend | max_spend | total_spend |
|---|---|---|---|---|---|
| Indore | 1 | 1 | 80 | 80 | 80 |
| Indore | 2 | 1 | 60 | 60 | 60 |
| Pune | 1 | 2 | 700 | 900 | 1600 |
| Pune | 2 | 1 | 700 | 700 | 700 |
| Pune | 3 | 1 | 250 | 250 | 250 |
| Pune | 4 | 1 | 250 | 250 | 250 |
How to solve PhonePe: Merchant-Spend Quartiles of Users in Each City, March 2025
Two levels again. First collapse payments to one March spend per user — only successful payments dated in March count, so user 6's failed ₹5,000 and April payment in the example leave them out — and carry the city along from the user table. Users with nothing to sum never form a row and are not bucketed.
Then NTILE(4) over each city's users, ordered by spend descending and user_id ascending, assigns the quartile: it splits n rows into four groups whose sizes differ by at most one, the larger ones first. Pune's five spenders become 2, 1, 1, 1 users; Indore's two users fill quartiles 1 and 2 only; Surat's only payment failed, so Surat has no row. Grouping by city and quartile gives each bucket's size, range and total. The user_id tie-break makes the dealing deterministic; and because tied users have equal spend, the bucket figures would not change whichever tied user landed where.
The alternative rebuilds NTILE from ROW_NUMBER and the city's count: with base = n DIV 4 and extra = n MOD 4, the first extra × (base + 1) rows go into groups of base + 1, the rest into groups of base. It is the same arithmetic NTILE performs, and it shows why a city of three users has no fourth quartile. Both are a sort per city.
Reference solution (MySQL)
WITH spend AS (
SELECT u.city, p.user_id, SUM(p.amount) AS spent
FROM P2mPayment p
JOIN AppUser u ON u.user_id = p.user_id
WHERE p.status = 'success' AND p.paid_on BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY u.city, p.user_id
), bucketed AS (
SELECT city, spent,
NTILE(4) OVER (PARTITION BY city ORDER BY spent DESC, user_id) AS quartile
FROM spend
)
SELECT city, quartile, COUNT(*) AS users,
MIN(spent) AS min_spend, MAX(spent) AS max_spend, SUM(spent) AS total_spend
FROM bucketed
GROUP BY city, quartile
ORDER BY city, quartileAnother way
WITH spend AS (
SELECT u.city, p.user_id, SUM(p.amount) AS spent FROM P2mPayment p JOIN AppUser u ON u.user_id = p.user_id
WHERE p.status = 'success' AND p.paid_on >= '2025-03-01' AND p.paid_on < '2025-04-01' GROUP BY u.city, p.user_id
), numbered AS (
SELECT city, spent,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY spent DESC, user_id) AS rn,
COUNT(*) OVER (PARTITION BY city) AS n
FROM spend
), sized AS (
SELECT city, spent, rn, n DIV 4 AS base, n MOD 4 AS extra FROM numbered
), bucketed AS (
SELECT city, spent,
CASE WHEN rn <= extra * (base + 1) THEN (rn - 1) DIV (base + 1) + 1
ELSE extra + (rn - extra * (base + 1) - 1) DIV base + 1 END AS quartile
FROM sized
)
SELECT city, quartile, COUNT(*) AS users, MIN(spent) AS min_spend, MAX(spent) AS max_spend, SUM(spent) AS total_spend
FROM bucketed GROUP BY city, quartile ORDER BY city, quartile← PhonePe: Bank Downtime Runs From Consecutive Technical Declines · PhonePe: Districts Whose State Rank Rose Every Quarter of 2024 →