PhonePe: New Users Who Tried Three Products in Their First 30 Days

Medium
888.

PhonePe: New Users Who Tried Three Products in Their First 30 Days

The app is more than UPI: users also recharge phones, buy insurance and buy digital gold. The cross-sell team measures how many new users try several of these products early on.

A user's first 30 days run from signup_date (day 0) to 29 days after it, both included. The four products are: a successful UPI payment, a successful mobile recharge, an insurance policy purchase, and a digital gold purchase. Return user_id, name and products_used, the number of different products the user used in their first 30 days, for every user who used at least 3. Activity before signup or after day 29 does not count. 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: UpiPayment

ColumnTypeValues
payment_idint
user_idint
statusenumsuccess, failed
paid_ondate

Primary key: payment_id.

One row per UPI payment attempt.

Table: MobileRecharge

ColumnTypeValues
recharge_idint
user_idint
statusenumsuccess, failed
recharged_ondate

Primary key: recharge_id.

One row per mobile recharge attempt.

Table: PolicyPurchase

ColumnType
policy_idint
user_idint
purchased_ondate

Primary key: policy_id.

One row per insurance policy bought in the app.

Table: GoldPurchase

ColumnType
order_idint
user_idint
milligramsint
bought_ondate

Primary key: order_id.

One row per digital gold purchase.

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-01
2Diya2025-01-05
3Kabir2025-01-10
4Meera2025-01-12

UpiPayment

payment_iduser_idstatuspaid_on
111success2025-01-01
121success2025-01-03
132failed2025-01-06
143success2025-01-15
154success2025-01-12

MobileRecharge

recharge_iduser_idstatusrecharged_on
211success2025-01-20
222success2025-01-07
233success2025-02-09
244failed2025-01-13

PolicyPurchase

policy_iduser_idpurchased_on
3112025-01-30
3222025-02-03
3332025-01-09
3442025-02-10

GoldPurchase

order_iduser_idmilligramsbought_on
4125002025-02-05
4232502025-02-10
4341002025-01-14

Output

user_idnameproducts_used
1Aarav3
4Meera3
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: New Users Who Tried Three Products in Their First 30 Days

The app is more than UPI: users also recharge phones, buy insurance and buy digital gold.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #888

Problem statement

The app is more than UPI: users also recharge phones, buy insurance and buy digital gold. The cross-sell team measures how many new users try several of these products early on.

A user's first 30 days run from signup_date (day 0) to 29 days after it, both included. The four products are: a successful UPI payment, a successful mobile recharge, an insurance policy purchase, and a digital gold purchase. Return user_id, name and products_used, the number of different products the user used in their first 30 days, for every user who used at least 3. Activity before signup or after day 29 does not count. 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: UpiPayment

ColumnType
payment_idint
user_idint
statusenum(success, failed)
paid_ondate

Primary key: payment_id.

One row per UPI payment attempt.

Table: MobileRecharge

ColumnType
recharge_idint
user_idint
statusenum(success, failed)
recharged_ondate

Primary key: recharge_id.

One row per mobile recharge attempt.

Table: PolicyPurchase

ColumnType
policy_idint
user_idint
purchased_ondate

Primary key: policy_id.

One row per insurance policy bought in the app.

Table: GoldPurchase

ColumnType
order_idint
user_idint
milligramsint
bought_ondate

Primary key: order_id.

One row per digital gold purchase.

Examples

Example 1

AppUser

user_idnamesignup_date
1Aarav2025-01-01
2Diya2025-01-05
3Kabir2025-01-10
4Meera2025-01-12

UpiPayment

payment_iduser_idstatuspaid_on
111success2025-01-01
121success2025-01-03
132failed2025-01-06
143success2025-01-15
154success2025-01-12

MobileRecharge

recharge_iduser_idstatusrecharged_on
211success2025-01-20
222success2025-01-07
233success2025-02-09
244failed2025-01-13

PolicyPurchase

policy_iduser_idpurchased_on
3112025-01-30
3222025-02-03
3332025-01-09
3442025-02-10

GoldPurchase

order_iduser_idmilligramsbought_on
4125002025-02-05
4232502025-02-10
4341002025-01-14

Output

user_idnameproducts_used
1Aarav3
4Meera3

How to solve PhonePe: New Users Who Tried Three Products in Their First 30 Days

The difficulty is that "a product used" is spread over four tables with different columns. Stacking them with UNION ALL into one shape — user_id, a constant product label, and the date — turns the question into a single aggregate. The success filters belong inside the branches they apply to: a failed UPI payment or recharge is not a use, while every policy and gold purchase row is one.

Joining the stack to the users with used_on BETWEEN signup_date AND DATE_ADD(signup_date, INTERVAL 29 DAY) keeps days 0 to 29 — the signup day counts, day 30 does not, and anything before signup is dropped. Then COUNT(DISTINCT a.product) per user counts products, not purchases: two gold orders still make one product. HAVING keeps users with three or more. In the example Aarav qualifies with his day-29 policy, and Meera with a signup-day payment, a day-29 policy and gold (her failed recharge is ignored). Kabir's policy came the day before signup and his recharge and gold on days 30 and 31, and Diya's only UPI attempt failed, so both stop short.

The alternative scores each product with its own EXISTS and adds the four 0/1 flags — no union and no DISTINCT, at the cost of four lookups per user. Both are linear with indexes on user_id in each table.

Reference solution (MySQL)

WITH activity AS (
  SELECT user_id, 'upi' AS product, paid_on AS used_on FROM UpiPayment WHERE status = 'success'
  UNION ALL
  SELECT user_id, 'recharge', recharged_on FROM MobileRecharge WHERE status = 'success'
  UNION ALL
  SELECT user_id, 'insurance', purchased_on FROM PolicyPurchase
  UNION ALL
  SELECT user_id, 'gold', bought_on FROM GoldPurchase
)
SELECT u.user_id, u.name, COUNT(DISTINCT a.product) AS products_used
FROM AppUser u
JOIN activity a ON a.user_id = u.user_id
 AND a.used_on BETWEEN u.signup_date AND DATE_ADD(u.signup_date, INTERVAL 29 DAY)
GROUP BY u.user_id, u.name
HAVING COUNT(DISTINCT a.product) >= 3

Another way

SELECT user_id, name, products_used FROM (
  SELECT u.user_id, u.name,
    (CASE WHEN EXISTS (SELECT 1 FROM UpiPayment p WHERE p.user_id = u.user_id AND p.status = 'success' AND DATEDIFF(p.paid_on, u.signup_date) BETWEEN 0 AND 29) THEN 1 ELSE 0 END)
  + (CASE WHEN EXISTS (SELECT 1 FROM MobileRecharge r WHERE r.user_id = u.user_id AND r.status = 'success' AND DATEDIFF(r.recharged_on, u.signup_date) BETWEEN 0 AND 29) THEN 1 ELSE 0 END)
  + (CASE WHEN EXISTS (SELECT 1 FROM PolicyPurchase i WHERE i.user_id = u.user_id AND DATEDIFF(i.purchased_on, u.signup_date) BETWEEN 0 AND 29) THEN 1 ELSE 0 END)
  + (CASE WHEN EXISTS (SELECT 1 FROM GoldPurchase g WHERE g.user_id = u.user_id AND DATEDIFF(g.bought_on, u.signup_date) BETWEEN 0 AND 29) THEN 1 ELSE 0 END) AS products_used
  FROM AppUser u
) t
WHERE products_used >= 3

← PhonePe: Collect-Request Senders Declined More Often Than Not · PhonePe: Users Who Only Send, Only Receive or Do Both in October →