Group loans into delinquency buckets with SQL CASE WHEN

A delinquency report is usually one table: how many loans, how much money and what share of the book sits in each bucket. SQL can produce it in a single query.

The table

The examples assume a loans table with three columns:

The query

CASE WHEN creates the buckets. A window function, SUM(...) OVER (), gives the total of all buckets so each row can show its share.

SELECT
  CASE
    WHEN days_past_due = 0 THEN 'Current'
    WHEN days_past_due BETWEEN 1 AND 30 THEN '1-30'
    WHEN days_past_due BETWEEN 31 AND 60 THEN '31-60'
    WHEN days_past_due BETWEEN 61 AND 90 THEN '61-90'
    ELSE '90+'
  END AS bucket,
  COUNT(*) AS loans,
  SUM(outstanding) AS outstanding,
  ROUND(100.0 * SUM(outstanding) / SUM(SUM(outstanding)) OVER (), 1) AS pct_of_book
FROM loans
GROUP BY 1
ORDER BY MIN(days_past_due);

Run on a six-loan sample book, it returns:

How it works

1. CASE WHEN tests each loan from top to bottom and stops at the first match, so the order of the conditions matters.
2. GROUP BY 1 groups by the first selected column, the bucket.
3. SUM(SUM(outstanding)) OVER () sums the bucket totals across all rows, giving the total book without a second query.
4. ORDER BY MIN(days_past_due) keeps the buckets in a sensible order instead of alphabetical.

What changes between databases

• SQL Server does not allow GROUP BY 1. Repeat the whole CASE expression in the GROUP BY clause.
• Rounding and integer division differ between engines. Multiplying by 100.0 rather than 100 forces decimal arithmetic.
• Window functions need a reasonably recent version of MySQL (8.0 or later). PostgreSQL, SQL Server and SQLite support them.

This is a sample article created to demonstrate the layout. The data is invented.