Paytm Money: SIPs That Bounced Twice in a Row — SQL Problem
On Paytm Money, a SIP instalment bounces when the bank mandate fails.
- Difficulty: Medium
- Topics: Window Functions, Joins
- Dialect: MySQL
- Problem: #859
Problem statement
On Paytm Money, a SIP instalment bounces when the bank mandate fails. Two bounces in a row usually mean the investor's account has a lasting problem, so the service team calls them.
Instalments of a SIP are numbered 1, 2, 3, … with no gaps. Return every SIP with two consecutive instalments (numbers n and n + 1) that both bounced, with the columns sip_id, investor_name, fund_name and streak_starts_at — the smallest n where such a pair begins. A skipped instalment between two bounces breaks the run. Return the rows ordered by sip_id.
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: Sip
| Column | Type |
|---|---|
| sip_id | int |
| investor_name | varchar |
| fund_name | varchar |
| monthly_amount | int |
Primary key: sip_id.
One row per systematic investment plan.
Table: SipInstalment
| Column | Type |
|---|---|
| sip_id | int |
| instalment_no | int |
| due_date | date |
| status | enum(paid, bounced, skipped) |
Primary key: sip_id, instalment_no.
One row per monthly instalment; skipped means the investor paused that month on purpose.
Examples
Example 1
Sip
| sip_id | investor_name | fund_name | monthly_amount |
|---|---|---|---|
| 1 | Aarav | Bluechip Equity Fund | 1000 |
| 2 | Diya | Midcap Growth Fund | 500 |
| 3 | Kabir | Liquid Fund | 2000 |
| 4 | Meera | Nifty Index Fund | 1500 |
SipInstalment
| sip_id | instalment_no | due_date | status |
|---|---|---|---|
| 1 | 1 | 2025-01-05 | paid |
| 1 | 2 | 2025-02-05 | bounced |
| 1 | 3 | 2025-03-05 | paid |
| 1 | 4 | 2025-04-05 | bounced |
| 2 | 1 | 2025-01-05 | paid |
| 2 | 2 | 2025-02-05 | bounced |
| 2 | 3 | 2025-03-05 | bounced |
| 2 | 4 | 2025-04-05 | bounced |
| 2 | 5 | 2025-05-05 | paid |
| 3 | 1 | 2025-01-05 | bounced |
| 3 | 2 | 2025-02-05 | skipped |
| 3 | 3 | 2025-03-05 | bounced |
| 4 | 1 | 2025-01-05 | bounced |
| 4 | 2 | 2025-02-05 | bounced |
| 4 | 3 | 2025-03-05 | paid |
| 4 | 4 | 2025-04-05 | bounced |
| 4 | 5 | 2025-05-05 | bounced |
Output
| sip_id | investor_name | fund_name | streak_starts_at |
|---|---|---|---|
| 2 | Diya | Midcap Growth Fund | 2 |
| 4 | Meera | Nifty Index Fund | 1 |
How to solve Paytm Money: SIPs That Bounced Twice in a Row
"Two in a row" is a question about neighbouring rows, so each instalment has to be put beside the next one. Because the numbers have no gaps, the next instalment of a is the row b of the same SIP with b.instalment_no = a.instalment_no + 1. Joining SipInstalment to itself on that rule, and requiring both sides to be bounced, leaves exactly the pairs that make a streak. A skipped month sits between two bounces as its own row, so neither bounce has a bounced neighbour and Kabir's SIP is correctly left out.
A SIP with a long run (Diya's 2-3-4) produces several pairs, and Meera has two separate runs; MIN(a.instalment_no) with GROUP BY collapses each SIP to one row and reports the first run's start.
The alternative uses LEAD(status) OVER (PARTITION BY sip_id ORDER BY instalment_no) to read the next status without a join; it would also cope with gaps in the numbering. Both are linear with an index on (sip_id, instalment_no).
Reference solution (MySQL)
SELECT s.sip_id, s.investor_name, s.fund_name, MIN(a.instalment_no) AS streak_starts_at
FROM Sip s
JOIN SipInstalment a ON a.sip_id = s.sip_id AND a.status = 'bounced'
JOIN SipInstalment b ON b.sip_id = a.sip_id AND b.instalment_no = a.instalment_no + 1 AND b.status = 'bounced'
GROUP BY s.sip_id, s.investor_name, s.fund_name
ORDER BY s.sip_idAnother way
WITH seqd AS (
SELECT sip_id, instalment_no, status,
LEAD(status) OVER (PARTITION BY sip_id ORDER BY instalment_no) AS next_status
FROM SipInstalment
)
SELECT s.sip_id, s.investor_name, s.fund_name, MIN(q.instalment_no) AS streak_starts_at
FROM seqd q JOIN Sip s ON s.sip_id = q.sip_id
WHERE q.status = 'bounced' AND q.next_status = 'bounced'
GROUP BY s.sip_id, s.investor_name, s.fund_name
ORDER BY s.sip_id← Paytm: Days Loan Deductions Took Over Half a Merchant's Settlement · Paytm: Scratch Cards That Expired Unscratched →