Build a roll-rate table in SQL

A delinquency report shows how many loans sit in each bucket today. It cannot tell you whether the book is getting healthier or sicker. For that you need roll rates: of the loans in a bucket at the end of one month, what share are in each bucket a month later?

If many loans in the 1-30 bucket roll into 31-60 every month, trouble is building even when today's totals look calm. If many cure back to current, the early-stage collections work is paying off.

This post builds a roll-rate table in one SQL query, using a small made-up sample so you can follow every number.

The table

The query needs one row per loan per month-end, so a loan appears once for each snapshot of the book:

The sample has 8 loans over three month-ends (January, February, March 2026). It is invented for teaching and is not real portfolio data.

To follow along, run this in any SQL tool (it works as written in SQLite, and the types are standard elsewhere):

CREATE TABLE loan_snapshots (loan_id INTEGER, month_end TEXT, days_past_due INTEGER, outstanding INTEGER);
INSERT INTO loan_snapshots VALUES
  (1,'2026-01-31',0,100000),(1,'2026-02-28',0,90000),(1,'2026-03-31',0,80000),
  (2,'2026-01-31',0,200000),(2,'2026-02-28',12,200000),(2,'2026-03-31',40,200000),
  (3,'2026-01-31',15,150000),(3,'2026-02-28',0,140000),(3,'2026-03-31',0,130000),
  (4,'2026-01-31',20,120000),(4,'2026-02-28',45,120000),(4,'2026-03-31',75,120000),
  (5,'2026-01-31',0,300000),(5,'2026-02-28',0,280000),(5,'2026-03-31',5,280000),
  (6,'2026-01-31',35,80000),(6,'2026-02-28',35,80000),(6,'2026-03-31',65,80000),
  (7,'2026-01-31',0,60000),(7,'2026-02-28',0,50000),
  (8,'2026-01-31',50,90000),(8,'2026-02-28',20,90000),(8,'2026-03-31',0,80000);

Loan 7 has no March row, which stands for a loan that left the book (repaid, for example).

Step 1: put each loan in a bucket

CASE WHEN turns days past due into a bucket. The numeric prefix in each label is deliberate: it makes the labels sort from healthiest to worst, which Step 3 relies on.

WITH bucketed AS (
  SELECT
    loan_id,
    month_end,
    outstanding,
    CASE
      WHEN days_past_due = 0  THEN '0 Current'
      WHEN days_past_due <= 30 THEN '1 (1-30)'
      WHEN days_past_due <= 60 THEN '2 (31-60)'
      ELSE '3 (61+)'
    END AS bucket
  FROM loan_snapshots
)

Step 2: look one month ahead with LEAD

LEAD reads a value from the next row inside a group. Grouping by loan and ordering by month means "the next row" is the same loan one month later.

, paired AS (
  SELECT
    loan_id,
    month_end,
    outstanding,
    bucket AS from_bucket,
    LEAD(bucket) OVER (PARTITION BY loan_id ORDER BY month_end) AS to_bucket
  FROM bucketed
)

Each row now holds a loan's bucket this month (from_bucket) and next month (to_bucket). For a loan's last snapshot there is no next row, so to_bucket is empty.

Step 3: count the moves

SELECT
  from_bucket,
  COALESCE(to_bucket, 'Left the book') AS to_bucket,
  COUNT(*) AS loans,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY from_bucket), 1) AS pct_of_from_bucket
FROM paired
WHERE month_end < (SELECT MAX(month_end) FROM loan_snapshots)
GROUP BY from_bucket, COALESCE(to_bucket, 'Left the book')
ORDER BY from_bucket, 2;

Two details matter here:

1. The WHERE clause drops the latest month. Those loans have no "next month" yet, and without this filter every loan would look as if it had left the book.
2. SUM(COUNT(*)) OVER (PARTITION BY from_bucket) totals each starting bucket, so every row can show its share of that bucket.

On the sample, the query returns:

Read it row by row. Of the loans that started a month in 1-30, half cured back to current and half rolled into 31-60. Of those that started in 31-60, half rolled into 61+. You can verify any line by tracing the loans in the sample by hand, which is a good habit before trusting a query on a real book.

Weight by money, not just loan counts

Counting loans treats a small loan and a large loan equally. Most portfolio reports care about balances. This version reports the share of the starting balance that rolled to a worse bucket or cured:

SELECT
  from_bucket,
  ROUND(100.0 * SUM(CASE WHEN to_bucket > from_bucket THEN outstanding ELSE 0 END) / SUM(outstanding), 1) AS pct_balance_rolled_worse,
  ROUND(100.0 * SUM(CASE WHEN to_bucket < from_bucket THEN outstanding ELSE 0 END) / SUM(outstanding), 1) AS pct_balance_cured
FROM paired
WHERE month_end < (SELECT MAX(month_end) FROM loan_snapshots)
GROUP BY from_bucket
ORDER BY from_bucket;

It compares the bucket labels as text, which is why the numeric prefixes in Step 1 matter. On the sample it gives:

Things to watch for

• Small samples mislead. Eight loans cannot say anything about a real book. The sample is for learning the mechanics. On real data, look at hundreds of loans per bucket before reading a rate as a trend.
• A missing month is not always a closed loan. The query treats a loan that disappears from the next snapshot as having left the book. If your snapshots can have gaps, or you need to separate paid off from written off, join in the loan status instead.
• Keep bucket definitions fixed. If the bucket edges change between reports, the rates are not comparable.
• Look at the trend. One month of roll rates is a snapshot. Run the same query for each pair of months and chart how the 1-30 to 31-60 rate moves over time.
• Window function support. LEAD and PARTITION BY work in PostgreSQL, SQL Server, MySQL 8 and later, BigQuery and SQLite 3.25 and later. Date and string handling differ slightly between engines, so test on your own database.

What to try next

Chart the "rolled worse" rate for each starting bucket by month, and add a column for the 90+ bucket if your book has one. A line that rises for several months in a row is an early warning that is worth a conversation with the collections team before the totals move.

Want to see the idea move? The roll-rate playground lets you change the rates with sliders and watch a made-up loan book respond.