Paytm Money: SIP Investor Retention Three Months Later by Cohort
The Paytm Money growth team measures whether new SIP investors are still investing a quarter later.
- Difficulty: Hard
- Topics: Subqueries, Dates, Aggregation
- Dialect: MySQL
- Problem: #868
Problem statement
The Paytm Money growth team measures whether new SIP investors are still investing a quarter later.
An investor's cohort is the month (YYYY-MM) of their first successful SIP payment; bounced payments never start a cohort. An investor is retained when they have at least one successful payment in the calendar month exactly three months after their cohort month (a January cohort looks at April; a November cohort at the next February). For every cohort return cohort_month, investors, retained and retention_pct = 100 × retained ÷ investors rounded to 1 decimal place. Return the rows ordered by cohort_month.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: SipPayment
| Column | Type |
|---|---|
| payment_id | int |
| investor_id | int |
| paid_on | date |
| amount | int |
| status | enum(success, bounced) |
Primary key: payment_id.
One row per SIP debit attempt.
Examples
Example 1
SipPayment
| payment_id | investor_id | paid_on | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2024-01-05 | 1000 | success |
| 2 | 1 | 2024-04-05 | 1000 | success |
| 3 | 2 | 2024-01-10 | 500 | bounced |
| 4 | 2 | 2024-02-10 | 500 | success |
| 5 | 2 | 2024-05-10 | 500 | success |
| 6 | 3 | 2024-01-20 | 2000 | success |
| 7 | 3 | 2024-02-20 | 2000 | success |
| 8 | 3 | 2024-03-20 | 2000 | success |
| 9 | 3 | 2024-04-20 | 2000 | bounced |
| 10 | 4 | 2024-02-01 | 1500 | success |
| 11 | 4 | 2024-05-31 | 1500 | bounced |
| 12 | 4 | 2024-06-01 | 1500 | success |
| 13 | 5 | 2024-11-15 | 3000 | success |
| 14 | 5 | 2025-02-28 | 3000 | success |
Output
| cohort_month | investors | retained | retention_pct |
|---|---|---|---|
| 2024-01 | 2 | 1 | 50 |
| 2024-02 | 2 | 1 | 50 |
| 2024-11 | 1 | 1 | 100 |
How to solve Paytm Money: SIP Investor Retention Three Months Later by Cohort
Cohort analysis is two steps: assign every member to a cohort, then measure each cohort. The cohort is the month of the investor's first successful payment — MIN(paid_on) over successful rows, formatted as YYYY-MM. Investor 2's bounced January debit does not count, so they belong to February.
Retention looks at one specific month: the cohort month plus three. DATE_ADD(CONCAT(cohort_month, '-01'), INTERVAL 3 MONTH) computes it with the calendar, so November 2024 → February 2025 just works. A correlated EXISTS asks whether the investor has a successful payment in that month and turns the answer into a 0/1 flag; a bounced payment in the target month (investor 4's 31 May) is not retention, and a payment a month later is too late.
Grouping the flags by cohort gives COUNT(*) investors and SUM(kept) retained, and the percentage is ROUND(100 * retained / investors, 1). Every cohort is reported, even one whose target month has no data yet.
The alternative works with month indexes (YEAR * 12 + MONTH), so "three months later" is + 3, finds retained investors with a join, and rebuilds the YYYY-MM label with DIV, MOD and LPAD.
Reference solution (MySQL)
WITH firsts AS (
SELECT investor_id, DATE_FORMAT(MIN(paid_on), '%Y-%m') AS cohort_month
FROM SipPayment
WHERE status = 'success'
GROUP BY investor_id
), flagged AS (
SELECT f.cohort_month,
CASE WHEN EXISTS (
SELECT 1 FROM SipPayment p
WHERE p.investor_id = f.investor_id AND p.status = 'success'
AND DATE_FORMAT(p.paid_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(f.cohort_month, '-01'), INTERVAL 3 MONTH), '%Y-%m')
) THEN 1 ELSE 0 END AS kept
FROM firsts f
)
SELECT cohort_month, COUNT(*) AS investors, SUM(kept) AS retained,
ROUND(100 * SUM(kept) / COUNT(*), 1) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
WITH ok AS (SELECT investor_id, YEAR(paid_on) * 12 + MONTH(paid_on) AS mi FROM SipPayment WHERE status = 'success'),
firsts AS (SELECT investor_id, MIN(mi) AS cm FROM ok GROUP BY investor_id),
kept AS (SELECT DISTINCT f.investor_id FROM firsts f JOIN ok o ON o.investor_id = f.investor_id AND o.mi = f.cm + 3)
SELECT CONCAT((f.cm - 1) DIV 12, '-', LPAD((f.cm - 1) MOD 12 + 1, 2, '0')) AS cohort_month,
COUNT(*) AS investors, COUNT(k.investor_id) AS retained,
ROUND(100 * COUNT(k.investor_id) / COUNT(*), 1) AS retention_pct
FROM firsts f LEFT JOIN kept k ON k.investor_id = f.investor_id
GROUP BY f.cm
ORDER BY cohort_month← Paytm: FASTag Reads Charged Off the Plaza's Axle-Band Rate · Paytm: Daily Seats Sold for Every Day of a Movie's Run →