Longest Run of Consecutive Missed Loan Instalments

Hard
67.

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. 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

ColumnTypeValues
loan_idint
installment_noint
due_datedate
statusenumPAID, PARTIAL, MISSED

Primary key (together): loan_id, installment_no.

One row per instalment that has fallen due.

Example

The rows must come back in the order shown.

EmiStatus

loan_idinstallment_nodue_datestatus
10112024-01-05PAID
10122024-02-05MISSED
10132024-03-05MISSED
10142024-04-05PAID
10152024-05-05MISSED
10162024-06-05MISSED
10212024-03-10MISSED
10222024-04-10PARTIAL
10232024-05-10MISSED
10312024-02-01MISSED
2 more rows

Output

loan_idlongest_missed_runrun_start
10331
10122
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.

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

ColumnType
loan_idint
installment_noint
due_datedate
statusenum(PAID, PARTIAL, MISSED)

Primary key: loan_id, installment_no.

One row per instalment that has fallen due.

Examples

Example 1

EmiStatus

loan_idinstallment_nodue_datestatus
10112024-01-05PAID
10122024-02-05MISSED
10132024-03-05MISSED
10142024-04-05PAID
10152024-05-05MISSED
10162024-06-05MISSED
10212024-03-10MISSED
10222024-04-10PARTIAL
10232024-05-10MISSED
10312024-02-01MISSED
10322024-03-01MISSED
10332024-04-01MISSED

Output

loan_idlongest_missed_runrun_start
10331
10122

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_id

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

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