Paytm Money: SIP Investor Retention Three Months Later by Cohort

Hard
868.

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.

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

ColumnTypeValues
payment_idint
investor_idint
paid_ondate
amountint
statusenumsuccess, bounced

Primary key: payment_id.

One row per SIP debit attempt.

Example

The rows must come back in the order shown.

SipPayment

payment_idinvestor_idpaid_onamountstatus
112024-01-051000success
212024-04-051000success
322024-01-10500bounced
422024-02-10500success
522024-05-10500success
632024-01-202000success
732024-02-202000success
832024-03-202000success
932024-04-202000bounced
1042024-02-011500success
4 more rows

Output

cohort_monthinvestorsretainedretention_pct
2024-012150
2024-022150
2024-1111100
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.

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

ColumnType
payment_idint
investor_idint
paid_ondate
amountint
statusenum(success, bounced)

Primary key: payment_id.

One row per SIP debit attempt.

Examples

Example 1

SipPayment

payment_idinvestor_idpaid_onamountstatus
112024-01-051000success
212024-04-051000success
322024-01-10500bounced
422024-02-10500success
522024-05-10500success
632024-01-202000success
732024-02-202000success
832024-03-202000success
932024-04-202000bounced
1042024-02-011500success
1142024-05-311500bounced
1242024-06-011500success
1352024-11-153000success
1452025-02-283000success

Output

cohort_monthinvestorsretainedretention_pct
2024-012150
2024-022150
2024-1111100

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_month

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