PhonePe: Users Whose First Successful Payment Was at a Merchant
Growth wants to know how users start: some first send money to a friend, others first scan a shop's QR. This question is about the second group.
- Difficulty: Medium
- Topics: Window Functions, Joins, Subqueries
- Dialect: MySQL
- Problem: #884
Problem statement
Growth wants to know how users start: some first send money to a friend, others first scan a shop's QR. This question is about the second group.
Return user_id, name and first_paid_at for every user whose earliest successful payment has txn_type = 'P2M'; first_paid_at is that payment's paid_at. Failed payments are ignored when finding the first one, and users with no successful payment are not returned. A user never has two payments at the same moment. 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: AppUser
| Column | Type |
|---|---|
| user_id | int |
| name | varchar |
| signup_date | date |
Primary key: user_id.
One row per registered user.
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| txn_type | enum(P2P, P2M) |
| status | enum(success, failed) |
| paid_at | datetime |
Primary key: payment_id.
One row per payment attempt by a user; user_id always names a user.
Examples
Example 1
AppUser
| user_id | name | signup_date |
|---|---|---|
| 1 | Aarav | 2025-01-02 |
| 2 | Diya | 2025-01-03 |
| 3 | Kabir | 2025-01-05 |
| 4 | Meera | 2025-01-06 |
Payment
| payment_id | user_id | txn_type | status | paid_at |
|---|---|---|---|---|
| 11 | 1 | P2M | success | 2025-01-02 10:00:00 |
| 12 | 1 | P2P | success | 2025-01-03 09:00:00 |
| 13 | 2 | P2M | failed | 2025-01-03 11:00:00 |
| 14 | 2 | P2P | success | 2025-01-03 11:05:00 |
| 15 | 2 | P2M | success | 2025-01-04 08:00:00 |
| 16 | 3 | P2P | failed | 2025-01-05 12:00:00 |
| 17 | 3 | P2M | success | 2025-01-06 19:30:00 |
| 18 | 4 | P2M | failed | 2025-01-07 07:00:00 |
Output
| user_id | name | first_paid_at |
|---|---|---|
| 1 | Aarav | 2025-01-02 10:00:00 |
| 3 | Kabir | 2025-01-06 19:30:00 |
How to solve PhonePe: Users Whose First Successful Payment Was at a Merchant
The order of operations is the whole problem. If you filter to P2M payments first and then take each user's earliest, every user who ever paid a merchant qualifies — Diya in the example, whose first success was a P2P transfer, would wrongly appear. The first successful payment has to be found among all successful payments, and only then is its type checked.
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY paid_at) over the successful rows numbers each user's payments in time order; the row numbered 1 is the first. Keeping rn = 1 AND txn_type = 'P2M' and joining the user table for the name gives the answer. Failed attempts never get a number, and a user with no successful payment has no row 1. Because a user's payments never share a timestamp, the first is unique.
The alternatives use the same idea without a window: compare paid_at with the user's MIN over successful payments, or keep a P2M success when NOT EXISTS an earlier success. With an index on (user_id, status, paid_at) each is close to linear.
Reference solution (MySQL)
WITH firsts AS (
SELECT user_id, txn_type, paid_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY paid_at) AS rn
FROM Payment
WHERE status = 'success'
)
SELECT u.user_id, u.name, f.paid_at AS first_paid_at
FROM firsts f
JOIN AppUser u ON u.user_id = f.user_id
WHERE f.rn = 1 AND f.txn_type = 'P2M'Another way
SELECT u.user_id, u.name, p.paid_at AS first_paid_at
FROM AppUser u JOIN Payment p ON p.user_id = u.user_id
WHERE p.status = 'success' AND p.txn_type = 'P2M'
AND p.paid_at = (SELECT MIN(q.paid_at) FROM Payment q WHERE q.user_id = u.user_id AND q.status = 'success')Another way
SELECT u.user_id, u.name, p.paid_at AS first_paid_at
FROM Payment p JOIN AppUser u ON u.user_id = p.user_id
WHERE p.status = 'success' AND p.txn_type = 'P2M'
AND NOT EXISTS (SELECT 1 FROM Payment q WHERE q.user_id = p.user_id AND q.status = 'success' AND q.paid_at < p.paid_at)← PhonePe: Top District by Transaction Count in Each State, Q2 2025 · PhonePe: Open Share.Market Positions From the Order Book →