UPI App New-User Retention in the Following Month
The growth team groups users into cohorts by the calendar month of their first UPI transaction, and measures how many came back in the next calendar month…
- Difficulty: Hard
- Topics: Dates, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #70
Problem statement
The growth team groups users into cohorts by the calendar month of their first UPI transaction, and measures how many came back in the next calendar month (any transaction in it counts; later months do not).
For every cohort, return cohort_month ('YYYY-MM'), new_users, retained_next_month and retention_pct — 100 × retained_next_month ÷ new_users, rounded to 2 decimals. Order the rows by cohort_month.
Tables
Table: UpiActivity
| Column | Type |
|---|---|
| txn_id | int |
| user_id | int |
| txn_date | date |
| amount | int |
Primary key: txn_id.
One row per successful UPI payment made in the app.
Examples
Example 1
UpiActivity
| txn_id | user_id | txn_date | amount |
|---|---|---|---|
| 1 | 1 | 2024-11-03 | 250 |
| 2 | 1 | 2024-12-15 | 90 |
| 3 | 2 | 2024-11-30 | 1200 |
| 4 | 2 | 2025-01-02 | 300 |
| 5 | 3 | 2024-12-01 | 45 |
| 6 | 3 | 2025-01-31 | 600 |
| 7 | 4 | 2024-12-20 | 5000 |
| 8 | 4 | 2024-12-22 | 150 |
| 9 | 5 | 2024-11-11 | 80 |
| 10 | 5 | 2024-11-25 | 75 |
| 11 | 5 | 2024-12-01 | 60 |
Output
| cohort_month | new_users | retained_next_month | retention_pct |
|---|---|---|---|
| 2024-11 | 3 | 2 | 66.67 |
| 2024-12 | 2 | 1 | 50 |
How to solve UPI App New-User Retention in the Following Month
Cohort analysis has two steps. First, each user's cohort: MIN(txn_date) per user, formatted as 'YYYY-MM'. Second, for each user, a yes/no — did they transact in the month after their cohort month? An EXISTS subquery that compares each transaction's month label with the label of DATE_ADD(cohort_month-01, INTERVAL 1 MONTH) answers it; anchoring at the 1st of the month avoids any end-of-month surprises when adding a month. Then group by cohort, count users, sum the flags, and round the percentage.
The details the statement pins down: activity two months later does not count, several transactions in the next month count once (the flag is 0/1 per user, not a count of transactions), and a user whose first transaction is on 30 November and next on 2 January is not retained for November. The year boundary must work — December's next month is January — which DATE_ADD handles.
The alternative converts months to integers (YEAR × 12 + MONTH), de-duplicates user-months, and LEFT JOINs each user's first month to the same user's month first + 1; COUNT(n.user_id) counts matches only. It is a cleaner plan for large tables — one aggregation and an equality join instead of a correlated subquery per user.
Reference solution (MySQL)
WITH firsts AS (
SELECT user_id, DATE_FORMAT(MIN(txn_date), '%Y-%m') AS cohort_month
FROM UpiActivity
GROUP BY user_id
), flagged AS (
SELECT f.user_id, f.cohort_month,
CASE WHEN EXISTS (
SELECT 1 FROM UpiActivity a
WHERE a.user_id = f.user_id
AND DATE_FORMAT(a.txn_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(f.cohort_month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
) THEN 1 ELSE 0 END AS came_back
FROM firsts f
)
SELECT cohort_month, COUNT(*) AS new_users, SUM(came_back) AS retained_next_month,
ROUND(100 * SUM(came_back) / COUNT(*), 2) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
WITH m AS (
SELECT DISTINCT user_id, YEAR(txn_date) * 12 + MONTH(txn_date) AS mi FROM UpiActivity
), firsts AS (
SELECT user_id, MIN(mi) AS first_mi FROM m GROUP BY user_id
)
SELECT CONCAT(FLOOR((f.first_mi - 1) / 12), '-', LPAD(MOD(f.first_mi - 1, 12) + 1, 2, '0')) AS cohort_month,
COUNT(*) AS new_users, COUNT(n.user_id) AS retained_next_month,
ROUND(COUNT(n.user_id) * 100 / COUNT(*), 2) AS retention_pct
FROM firsts f LEFT JOIN m n ON n.user_id = f.user_id AND n.mi = f.first_mi + 1
GROUP BY f.first_mi ORDER BY cohort_month← Median Card Spend by Merchant Category · Top Two PSP Handles by Monthly UPI Volume →