Longest Run of Consecutive Missed Loan Instalments
Missing several EMIs in a row is a far stronger default signal than the same number of scattered misses.
- Difficulty: Hard
- Topics: Window Functions, Subqueries
- Dialect: MySQL
- Problem: #67
Problem statement
Missing several EMIs in a row is a far stronger default signal than the same number of scattered misses. For each loan, find its longest run of consecutive instalments with status MISSED (a PARTIAL payment breaks a run, like a PAID one). Every loan's instalments are numbered 1, 2, 3, … with no gaps.
Return loan_id, longest_missed_run (the run's length) and run_start (the installment_no where it starts — the earliest such run if two runs tie) for every loan whose longest run is at least 2. Order the rows by longest_missed_run descending, then by loan_id.
Tables
Table: EmiStatus
| Column | Type |
|---|---|
| loan_id | int |
| installment_no | int |
| due_date | date |
| status | enum(PAID, PARTIAL, MISSED) |
Primary key: loan_id, installment_no.
One row per instalment that has fallen due.
Examples
Example 1
EmiStatus
| loan_id | installment_no | due_date | status |
|---|---|---|---|
| 101 | 1 | 2024-01-05 | PAID |
| 101 | 2 | 2024-02-05 | MISSED |
| 101 | 3 | 2024-03-05 | MISSED |
| 101 | 4 | 2024-04-05 | PAID |
| 101 | 5 | 2024-05-05 | MISSED |
| 101 | 6 | 2024-06-05 | MISSED |
| 102 | 1 | 2024-03-10 | MISSED |
| 102 | 2 | 2024-04-10 | PARTIAL |
| 102 | 3 | 2024-05-10 | MISSED |
| 103 | 1 | 2024-02-01 | MISSED |
| 103 | 2 | 2024-03-01 | MISSED |
| 103 | 3 | 2024-04-01 | MISSED |
Output
| loan_id | longest_missed_run | run_start |
|---|---|---|
| 103 | 3 | 1 |
| 101 | 2 | 2 |
How to solve Longest Run of Consecutive Missed Loan Instalments
This is a gaps-and-islands problem. Filter to the missed instalments and number them per loan with ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY installment_no). Along a run of consecutive instalments both numbers grow by one, so their difference stays constant; when a paid or partial instalment interrupts, installment_no jumps while the row number does not, and the difference changes. That difference is a run label.
Group by loan and label to get each run's MIN(installment_no) (its start) and COUNT(*) (its length). Then pick each loan's best run with another ROW_NUMBER, ordered by length descending and start ascending — the tie rule in the statement — and keep it when the length is at least 2.
Two alternatives reach the same runs differently. One counts the non-missed instalments seen so far (a running SUM of a break flag); missed rows sharing that count form one run. The other finds run starts with NOT EXISTS (the previous instalment was not missed) and measures each run up to the next non-missed instalment, or to the end of the schedule. The window plans are a couple of sorts; the correlated one is quadratic per loan.
Reference solution (MySQL)
WITH missed AS (
SELECT loan_id, installment_no,
installment_no - ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY installment_no) AS grp
FROM EmiStatus
WHERE status = 'MISSED'
), runs AS (
SELECT loan_id, MIN(installment_no) AS run_start, COUNT(*) AS run_len
FROM missed
GROUP BY loan_id, grp
), ranked AS (
SELECT loan_id, run_start, run_len,
ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY run_len DESC, run_start) AS rn
FROM runs
)
SELECT loan_id, run_len AS longest_missed_run, run_start
FROM ranked
WHERE rn = 1 AND run_len >= 2
ORDER BY longest_missed_run DESC, loan_idAnother way
WITH starts AS (
SELECT m.loan_id, m.installment_no AS run_start,
COALESCE((SELECT MIN(x.installment_no) FROM EmiStatus x WHERE x.loan_id = m.loan_id AND x.installment_no > m.installment_no AND x.status <> 'MISSED'),
(SELECT MAX(y.installment_no) + 1 FROM EmiStatus y WHERE y.loan_id = m.loan_id)) - m.installment_no AS run_len
FROM EmiStatus m
WHERE m.status = 'MISSED'
AND NOT EXISTS (SELECT 1 FROM EmiStatus p WHERE p.loan_id = m.loan_id AND p.installment_no = m.installment_no - 1 AND p.status = 'MISSED')
)
SELECT s.loan_id, s.run_len AS longest_missed_run, s.run_start FROM starts s
WHERE s.run_len >= 2
AND NOT EXISTS (SELECT 1 FROM starts o WHERE o.loan_id = s.loan_id
AND (o.run_len > s.run_len OR (o.run_len = s.run_len AND o.run_start < s.run_start)))
ORDER BY longest_missed_run DESC, s.loan_idAnother way
WITH flagged AS (
SELECT loan_id, installment_no, status,
SUM(CASE WHEN status <> 'MISSED' THEN 1 ELSE 0 END) OVER (PARTITION BY loan_id ORDER BY installment_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS breaks
FROM EmiStatus
), runs AS (
SELECT loan_id, breaks, MIN(installment_no) AS run_start, COUNT(*) AS run_len FROM flagged WHERE status = 'MISSED' GROUP BY loan_id, breaks
), best AS (
SELECT loan_id, MAX(run_len) AS top_len FROM runs GROUP BY loan_id
)
SELECT r.loan_id, r.run_len AS longest_missed_run, MIN(r.run_start) AS run_start
FROM runs r JOIN best b ON b.loan_id = r.loan_id AND b.top_len = r.run_len
WHERE r.run_len >= 2 GROUP BY r.loan_id, r.run_len ORDER BY longest_missed_run DESC, r.loan_id← Rapid Repeated UPI Transfers: a Fraud Signal · Savings Accounts Below the Minimum Average Balance →