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
| Column | Type |
|---|---|
| user_id | int |
| name | varchar |
| signup_date | date |
Primary key: user_id.
One row per registered user.
Table: UpiPayment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| status | enum(success, failed) |
| paid_on | date |
Primary key: payment_id.
One row per UPI payment attempt.
Table: MobileRecharge
| Column | Type |
|---|---|
| recharge_id | int |
| user_id | int |
| status | enum(success, failed) |
| recharged_on | date |
Primary key: recharge_id.
One row per mobile recharge attempt.
Table: PolicyPurchase
| Column | Type |
|---|---|
| policy_id | int |
| user_id | int |
| purchased_on | date |
Primary key: policy_id.
One row per insurance policy bought in the app.
Table: GoldPurchase
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| milligrams | int |
| bought_on | date |
Primary key: order_id.
One row per digital gold purchase.
Examples
Example 1
AppUser
| user_id | name | signup_date |
|---|---|---|
| 1 | Aarav | 2025-01-01 |
| 2 | Diya | 2025-01-05 |
| 3 | Kabir | 2025-01-10 |
| 4 | Meera | 2025-01-12 |
UpiPayment
| payment_id | user_id | status | paid_on |
|---|---|---|---|
| 11 | 1 | success | 2025-01-01 |
| 12 | 1 | success | 2025-01-03 |
| 13 | 2 | failed | 2025-01-06 |
| 14 | 3 | success | 2025-01-15 |
| 15 | 4 | success | 2025-01-12 |
MobileRecharge
| recharge_id | user_id | status | recharged_on |
|---|---|---|---|
| 21 | 1 | success | 2025-01-20 |
| 22 | 2 | success | 2025-01-07 |
| 23 | 3 | success | 2025-02-09 |
| 24 | 4 | failed | 2025-01-13 |
PolicyPurchase
| policy_id | user_id | purchased_on |
|---|---|---|
| 31 | 1 | 2025-01-30 |
| 32 | 2 | 2025-02-03 |
| 33 | 3 | 2025-01-09 |
| 34 | 4 | 2025-02-10 |
GoldPurchase
| order_id | user_id | milligrams | bought_on |
|---|---|---|---|
| 41 | 2 | 500 | 2025-02-05 |
| 42 | 3 | 250 | 2025-02-10 |
| 43 | 4 | 100 | 2025-01-14 |
Output
| user_id | name | products_used |
|---|---|---|
| 1 | Aarav | 3 |
| 4 | Meera | 3 |
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) >= 3Another 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 →