PhonePe: Merchant-Spend Quartiles of Users in Each City, March 2025

Hard
894.

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. 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

ColumnType
user_idint
cityvarchar

Primary key: user_id.

One row per user, with the city of their registered address.

Table: P2mPayment

ColumnTypeValues
payment_idint
user_idint
amountint
statusenumsuccess, failed
paid_ondate

Primary key: payment_id.

One row per payment by a user to a merchant; user_id always names a user.

Example

The rows must come back in the order shown.

AppUser

user_idcity
1Pune
2Pune
3Pune
4Pune
5Pune
6Pune
7Indore
8Indore
9Surat

P2mPayment

payment_iduser_idamountstatuspaid_on
111900success2025-03-02
122400success2025-03-05
132300success2025-03-25
143700success2025-03-09
154250success2025-03-31
165250success2025-03-12
1765000failed2025-03-14
186120success2025-04-01
19760success2025-03-03
20880success2025-03-04
1 more row

Output

cityquartileusersmin_spendmax_spendtotal_spend
Indore11808080
Indore21606060
Pune127009001600
Pune21700700700
Pune31250250250
Pune41250250250
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: 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

ColumnType
user_idint
cityvarchar

Primary key: user_id.

One row per user, with the city of their registered address.

Table: P2mPayment

ColumnType
payment_idint
user_idint
amountint
statusenum(success, failed)
paid_ondate

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_idcity
1Pune
2Pune
3Pune
4Pune
5Pune
6Pune
7Indore
8Indore
9Surat

P2mPayment

payment_iduser_idamountstatuspaid_on
111900success2025-03-02
122400success2025-03-05
132300success2025-03-25
143700success2025-03-09
154250success2025-03-31
165250success2025-03-12
1765000failed2025-03-14
186120success2025-04-01
19760success2025-03-03
20880success2025-03-04
21950failed2025-03-04

Output

cityquartileusersmin_spendmax_spendtotal_spend
Indore11808080
Indore21606060
Pune127009001600
Pune21700700700
Pune31250250250
Pune41250250250

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, quartile

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