Paytm Money: SIPs That Bounced Twice in a Row

Medium
859.

Paytm Money: SIPs That Bounced Twice in a Row

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

ColumnType
sip_idint
investor_namevarchar
fund_namevarchar
monthly_amountint

Primary key: sip_id.

One row per systematic investment plan.

Table: SipInstalment

ColumnTypeValues
sip_idint
instalment_noint
due_datedate
statusenumpaid, bounced, skipped

Primary key (together): sip_id, instalment_no.

One row per monthly instalment; skipped means the investor paused that month on purpose.

Example

The rows must come back in the order shown.

Sip

sip_idinvestor_namefund_namemonthly_amount
1AaravBluechip Equity Fund1000
2DiyaMidcap Growth Fund500
3KabirLiquid Fund2000
4MeeraNifty Index Fund1500

SipInstalment

sip_idinstalment_nodue_datestatus
112025-01-05paid
122025-02-05bounced
132025-03-05paid
142025-04-05bounced
212025-01-05paid
222025-02-05bounced
232025-03-05bounced
242025-04-05bounced
252025-05-05paid
312025-01-05bounced
7 more rows

Output

sip_idinvestor_namefund_namestreak_starts_at
2DiyaMidcap Growth Fund2
4MeeraNifty Index Fund1
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: 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

ColumnType
sip_idint
investor_namevarchar
fund_namevarchar
monthly_amountint

Primary key: sip_id.

One row per systematic investment plan.

Table: SipInstalment

ColumnType
sip_idint
instalment_noint
due_datedate
statusenum(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_idinvestor_namefund_namemonthly_amount
1AaravBluechip Equity Fund1000
2DiyaMidcap Growth Fund500
3KabirLiquid Fund2000
4MeeraNifty Index Fund1500

SipInstalment

sip_idinstalment_nodue_datestatus
112025-01-05paid
122025-02-05bounced
132025-03-05paid
142025-04-05bounced
212025-01-05paid
222025-02-05bounced
232025-03-05bounced
242025-04-05bounced
252025-05-05paid
312025-01-05bounced
322025-02-05skipped
332025-03-05bounced
412025-01-05bounced
422025-02-05bounced
432025-03-05paid
442025-04-05bounced
452025-05-05bounced

Output

sip_idinvestor_namefund_namestreak_starts_at
2DiyaMidcap Growth Fund2
4MeeraNifty Index Fund1

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_id

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