Learn / Data guide

SQL for credit risk: 10 queries every analyst should know

By the Bucketwise team · 10 min read · Updated 13 November 2026

These ten queries cover most of what a credit risk analyst is asked for in a month. Each one was run on a sample portfolio and its output was compared with a separate calculation. Change the names and the as-of date, then check the result against your own numbers.

Which tables do these queries use?

• Buckets are Current, 1-30, 31-60, 61-90 and 91+. The 91+ bucket means more than 90 days past due, which is the NPA test for term loans.
• Dates are written as text in the form 2026-09-30. Replace 2026-09-30 with your as-of date.
• pos_amt is principal outstanding. mob is months on book.
• We ran the queries on SQLite. They use standard SQL (CASE, joins, window functions), so they should port, but test on your own database and adjust the date literals if your columns are real dates.

If your tables look different, the data guide shows where each column usually lives.

1. Bucket distribution at a month-end

How many loans and how much principal sit in each bucket. This is the first table in most portfolio reports.

SELECT bucket,
       COUNT(*)            AS loans,
       SUM(pos_amt)        AS pos
FROM   loan_month
WHERE  month_end = '2026-09-30'
GROUP  BY bucket
ORDER  BY MIN(dpd);

Check: the loan counts add up to the loans live at that month-end, and the principal adds up to your finance figure.

2. Gross NPA ratio by month

Principal in loans more than 90 days past due, as a share of total principal, for every month.

SELECT month_end,
       SUM(CASE WHEN dpd > 90 THEN pos_amt ELSE 0 END) AS npa_pos,
       SUM(pos_amt)                                    AS total_pos,
       100.0 * SUM(CASE WHEN dpd > 90 THEN pos_amt ELSE 0 END)
             / SUM(pos_amt)                            AS gross_npa_pct
FROM   loan_month
GROUP  BY month_end
ORDER  BY month_end;

Check: this is a balance-based ratio. A count-based figure from the bureau will differ. See the gross NPA vs bureau 90+ guide.

3. Roll rate: where last month's buckets went

For every bucket last month, how many loans are in each bucket this month. Divide each cell by its row total to get the matrix.

WITH x AS (
  SELECT loan_id, month_end, bucket,
         LAG(bucket) OVER (PARTITION BY loan_id ORDER BY month_end) AS prev_bucket
  FROM   loan_month
)
SELECT prev_bucket AS from_bucket,
       bucket      AS to_bucket,
       COUNT(*)    AS loans
FROM   x
WHERE  month_end = '2026-09-30'
  AND  prev_bucket IS NOT NULL
GROUP  BY prev_bucket, bucket
ORDER  BY prev_bucket, bucket;

Check: the rows only cover loans present in both months. New and closed loans need their own handling, as the roll rate guide explains.

4. Vintage: share of each cohort that went 31+ by month 3

Groups loans by the month they were disbursed and counts those that were more than 30 days past due at any point in their first three months on book. It leaves out loans younger than three months.

SELECT l.disb_month                                   AS vintage,
       COUNT(*)                                          AS loans,
       100.0 * SUM(CASE WHEN m.ever31 = 1 THEN 1 ELSE 0 END)
             / COUNT(*)                                  AS ever_31plus_by_mob3_pct
FROM   loans l
JOIN  (SELECT loan_id,
              MAX(CASE WHEN dpd > 30 AND mob <= 3 THEN 1 ELSE 0 END) AS ever31
       FROM   loan_month
       GROUP  BY loan_id
       HAVING MAX(mob) >= 3) m
  ON   m.loan_id = l.loan_id
GROUP  BY l.disb_month
ORDER  BY l.disb_month;

Check: compare cohorts at the same age only. Change 30 and 3 to your own bad definition and window.

5. Cure rate

Of the loans in the 1-30 bucket last month, the share that are Current this month.

WITH x AS (
  SELECT loan_id, month_end, bucket,
         LAG(bucket) OVER (PARTITION BY loan_id ORDER BY month_end) AS prev_bucket
  FROM   loan_month
)
SELECT month_end,
       COUNT(*) AS loans_in_1_30_last_month,
       SUM(CASE WHEN bucket = 'Current' THEN 1 ELSE 0 END) AS cured,
       100.0 * SUM(CASE WHEN bucket = 'Current' THEN 1 ELSE 0 END)
             / COUNT(*) AS cure_rate_pct
FROM   x
WHERE  prev_bucket = '1-30'
GROUP  BY month_end
ORDER  BY month_end;

Check: a falling cure rate is an early sign that collections are losing grip.

6. Concentration: share of the top 10 loans

Principal in the ten largest loans as a share of the whole book.

WITH r AS (
  SELECT loan_id, pos_amt,
         ROW_NUMBER() OVER (ORDER BY pos_amt DESC, loan_id) AS rn
  FROM   loan_month
  WHERE  month_end = '2026-09-30'
)
SELECT 100.0 * SUM(CASE WHEN rn <= 10 THEN pos_amt ELSE 0 END)
             / SUM(pos_amt) AS top10_share_pct
FROM   r;

Check: ties at the cut-off are broken by loan_id so the result is repeatable.

7. Duplicate rows

Any loan with more than one row for the same month-end. It should return nothing.

SELECT loan_id, month_end, COUNT(*) AS rows_found
FROM   loan_month
GROUP  BY loan_id, month_end
HAVING COUNT(*) > 1;

Run this before you trust any total. A duplicated row doubles that loan's principal.

8. DPD and overdue amount that disagree

Loans that show days past due but no overdue amount, or an overdue amount with zero days past due.

SELECT loan_id, month_end, dpd, overdue_amt
FROM   loan_month
WHERE  (dpd > 0 AND overdue_amt = 0)
   OR  (dpd = 0 AND overdue_amt > 0);

Check: each row is a data problem or a timing difference. Find out which before you report.

9. Collection efficiency

Current CE, total CE and the share of loans that paid their current demand in full, by month.

SELECT month_end,
       100.0 * SUM(coll_current) / SUM(current_demand)            AS current_ce_pct,
       100.0 * SUM(coll_current + coll_overdue)
             / SUM(current_demand + overdue_bf)                   AS total_ce_pct,
       100.0 * SUM(CASE WHEN coll_current >= current_demand THEN 1 ELSE 0 END)
             / COUNT(*)                                           AS paid_in_full_pct
FROM   demand_collection
GROUP  BY month_end
ORDER  BY month_end;

The collection efficiency guide explains the three versions.

10. Gross NPA by product

Loans, principal and gross NPA ratio for each product at one month-end.

SELECT l.product,
       COUNT(*)                                         AS loans,
       SUM(m.pos_amt)                                   AS pos,
       100.0 * SUM(CASE WHEN m.dpd > 90 THEN m.pos_amt ELSE 0 END)
             / SUM(m.pos_amt)                           AS gross_npa_pct
FROM   loan_month m
JOIN   loans l ON l.loan_id = m.loan_id
WHERE  m.month_end = '2026-09-30'
GROUP  BY l.product
ORDER  BY gross_npa_pct DESC;

Check: the product ratios, weighted by principal, should reproduce the total from query 2.

What should you check before you run any of these?

• Run the duplicate check first.
• Run on one month-end and trace ten loans by hand before you run twelve months.
• Tie the totals to a finance or MIS figure.
• Write down the bad definition and the bucket boundaries next to the result.

Frequently asked questions

Keep reading