PhonePe: Users Whose First Successful Payment Was at a Merchant

Medium
884.

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.

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

ColumnType
user_idint
namevarchar
signup_datedate

Primary key: user_id.

One row per registered user.

Table: Payment

ColumnTypeValues
payment_idint
user_idint
txn_typeenumP2P, P2M
statusenumsuccess, failed
paid_atdatetime

Primary key: payment_id.

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

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AppUser

user_idnamesignup_date
1Aarav2025-01-02
2Diya2025-01-03
3Kabir2025-01-05
4Meera2025-01-06

Payment

payment_iduser_idtxn_typestatuspaid_at
111P2Msuccess2025-01-02 10:00:00
121P2Psuccess2025-01-03 09:00:00
132P2Mfailed2025-01-03 11:00:00
142P2Psuccess2025-01-03 11:05:00
152P2Msuccess2025-01-04 08:00:00
163P2Pfailed2025-01-05 12:00:00
173P2Msuccess2025-01-06 19:30:00
184P2Mfailed2025-01-07 07:00:00

Output

user_idnamefirst_paid_at
1Aarav2025-01-02 10:00:00
3Kabir2025-01-06 19:30:00
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: 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

ColumnType
user_idint
namevarchar
signup_datedate

Primary key: user_id.

One row per registered user.

Table: Payment

ColumnType
payment_idint
user_idint
txn_typeenum(P2P, P2M)
statusenum(success, failed)
paid_atdatetime

Primary key: payment_id.

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

Examples

Example 1

AppUser

user_idnamesignup_date
1Aarav2025-01-02
2Diya2025-01-03
3Kabir2025-01-05
4Meera2025-01-06

Payment

payment_iduser_idtxn_typestatuspaid_at
111P2Msuccess2025-01-02 10:00:00
121P2Psuccess2025-01-03 09:00:00
132P2Mfailed2025-01-03 11:00:00
142P2Psuccess2025-01-03 11:05:00
152P2Msuccess2025-01-04 08:00:00
163P2Pfailed2025-01-05 12:00:00
173P2Msuccess2025-01-06 19:30:00
184P2Mfailed2025-01-07 07:00:00

Output

user_idnamefirst_paid_at
1Aarav2025-01-02 10:00:00
3Kabir2025-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 →