PhonePe: Merchants Earning Half Their Takings From Three Customers

Hard
896.

PhonePe: Merchants Earning Half Their Takings From Three Customers

Merchant-lending looks at how concentrated a shop's income is before offering a loan: a shop that lives off a few regular customers is a different risk from one with a broad base.

Using successful payments made in March 2025 only, find merchants that had at least 5 different paying customers in that month and whose three biggest customers (by their total March spend at that merchant) paid at least half of the merchant's March takings. Return merchant_id, march_takings and top3_takings. When customers tie for third place, any of them may fill the slot — the sum is the same. Order the rows by merchant_id.

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: MerchantPayment

ColumnTypeValues
payment_idint
merchant_idint
customer_idint
amountint
statusenumsuccess, failed
paid_ondate

Primary key: payment_id.

One row per payment from a customer to a merchant.

Example

The rows must come back in the order shown.

MerchantPayment

payment_idmerchant_idcustomer_idamountstatuspaid_on
1111300success2025-03-02
2111200success2025-03-20
3112300success2025-03-05
4113100success2025-03-06
5114100success2025-03-07
6115100success2025-03-08
7116100success2025-03-31
8211100success2025-03-01
9212100success2025-03-02
10213100success2025-03-03
9 more rows

Output

merchant_idmarch_takingstop3_takings
11200900
2600300
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: Merchants Earning Half Their Takings From Three Customers

Merchant-lending looks at how concentrated a shop's income is before offering a loan: a shop that lives off a few regular customers is a different risk from…

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

Problem statement

Merchant-lending looks at how concentrated a shop's income is before offering a loan: a shop that lives off a few regular customers is a different risk from one with a broad base.

Using successful payments made in March 2025 only, find merchants that had at least 5 different paying customers in that month and whose three biggest customers (by their total March spend at that merchant) paid at least half of the merchant's March takings. Return merchant_id, march_takings and top3_takings. When customers tie for third place, any of them may fill the slot — the sum is the same. Order the rows by merchant_id.

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: MerchantPayment

ColumnType
payment_idint
merchant_idint
customer_idint
amountint
statusenum(success, failed)
paid_ondate

Primary key: payment_id.

One row per payment from a customer to a merchant.

Examples

Example 1

MerchantPayment

payment_idmerchant_idcustomer_idamountstatuspaid_on
1111300success2025-03-02
2111200success2025-03-20
3112300success2025-03-05
4113100success2025-03-06
5114100success2025-03-07
6115100success2025-03-08
7116100success2025-03-31
8211100success2025-03-01
9212100success2025-03-02
10213100success2025-03-03
11214100success2025-03-04
12215100success2025-03-05
13216100success2025-03-06
14217900failed2025-03-07
15311800success2025-03-10
1631250success2025-03-11
1731350success2025-03-12
1831450success2025-03-13
19315700success2025-04-01

Output

merchant_idmarch_takingstop3_takings
11200900
2600300

How to solve PhonePe: Merchants Earning Half Their Takings From Three Customers

The unit of the ranking is a customer, so the first step collapses payments into one total per (merchant, customer), keeping only successful payments dated inside March — merchant 3's April payment and merchant 2's failed one are gone before anything is ranked. Each merchant's number of rows is then its number of distinct paying customers, so COUNT(*) >= 5 is the breadth test (merchant 3 has only four).

ROW_NUMBER() over each merchant's customers ordered by spend (descending) numbers them from the biggest down; the sum of the spend where rn <= 3 is the top three's share, and the sum of all is the takings. Ties at third place do not change that sum — whichever tied customer gets number 3, the amount added is the same — and the customer_id tie-break only makes the numbering deterministic. RANK() would be wrong here: four customers tied at the top would all get rank 1 and be added. Merchant 2 shows the boundary: 300 of 600 is exactly half, which counts.

The alternative ranks without a window: a customer is in the top three when fewer than three customers of the same merchant come before it in (spend desc, id asc) order. It is quadratic per merchant, where the window is one sort.

Reference solution (MySQL)

WITH spend AS (
  SELECT merchant_id, customer_id, SUM(amount) AS spent
  FROM MerchantPayment
  WHERE status = 'success' AND paid_on BETWEEN '2025-03-01' AND '2025-03-31'
  GROUP BY merchant_id, customer_id
), ranked AS (
  SELECT merchant_id, spent,
         ROW_NUMBER() OVER (PARTITION BY merchant_id ORDER BY spent DESC, customer_id) AS rn
  FROM spend
)
SELECT merchant_id, SUM(spent) AS march_takings,
       SUM(CASE WHEN rn <= 3 THEN spent ELSE 0 END) AS top3_takings
FROM ranked
GROUP BY merchant_id
HAVING COUNT(*) >= 5
   AND 2 * SUM(CASE WHEN rn <= 3 THEN spent ELSE 0 END) >= SUM(spent)
ORDER BY merchant_id

Another way

WITH spend AS (SELECT merchant_id, customer_id, SUM(amount) AS spent FROM MerchantPayment WHERE status = 'success' AND paid_on >= '2025-03-01' AND paid_on < '2025-04-01' GROUP BY merchant_id, customer_id),
top3 AS (
  SELECT a.merchant_id, SUM(a.spent) AS top3_takings FROM spend a
  WHERE (SELECT COUNT(*) FROM spend b WHERE b.merchant_id = a.merchant_id
         AND (b.spent > a.spent OR (b.spent = a.spent AND b.customer_id < a.customer_id))) < 3
  GROUP BY a.merchant_id
), tot AS (SELECT merchant_id, SUM(spent) AS march_takings, COUNT(*) AS customers FROM spend GROUP BY merchant_id)
SELECT t.merchant_id, t.march_takings, p.top3_takings FROM tot t JOIN top3 p ON p.merchant_id = t.merchant_id
WHERE t.customers >= 5 AND 2 * p.top3_takings >= t.march_takings
ORDER BY t.merchant_id

← PhonePe: Districts Whose State Rank Rose Every Quarter of 2024 · PhonePe: Three-Way Circular Transfers Within 24 Hours →