UPI App New-User Retention in the Following Month

Hard
70.

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 (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

ColumnType
txn_idint
user_idint
txn_datedate
amountint

Primary key: txn_id.

One row per successful UPI payment made in the app.

Example

The rows must come back in the order shown.

UpiActivity

txn_iduser_idtxn_dateamount
112024-11-03250
212024-12-1590
322024-11-301200
422025-01-02300
532024-12-0145
632025-01-31600
742024-12-205000
842024-12-22150
952024-11-1180
1052024-11-2575
1 more row

Output

cohort_monthnew_usersretained_next_monthretention_pct
2024-113266.67
2024-122150
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.

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

ColumnType
txn_idint
user_idint
txn_datedate
amountint

Primary key: txn_id.

One row per successful UPI payment made in the app.

Examples

Example 1

UpiActivity

txn_iduser_idtxn_dateamount
112024-11-03250
212024-12-1590
322024-11-301200
422025-01-02300
532024-12-0145
632025-01-31600
742024-12-205000
842024-12-22150
952024-11-1180
1052024-11-2575
1152024-12-0160

Output

cohort_monthnew_usersretained_next_monthretention_pct
2024-113266.67
2024-122150

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_month

Another 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 →