If you join a credit-risk team — a bank, a fintech lender, a BNPL, an auto loan portfolio — by year two you will own two queries: the vintage curve and the roll-rate matrix. They sit behind every portfolio review, every board slide on credit quality, every loss forecast. Getting them right is table stakes. Getting them right *quickly* is the difference between a seat at the weekly risk review and a seat at the junior desk.

This post is the practical how-to: what each one is, why stakeholders care, how to write it from a realistic banking schema, and how it gets asked about in an interview. Everything here runs against the caseSQL banking schema — `dim_loans`, `fact_originations`, `fact_loan_status` — so you can try each query end-to-end.

A **vintage curve** tracks a cohort of loans originated in the same month, measuring how they perform over their lifetime. The x-axis is "months on book" (MOB): 1, 2, 3, ... 24. The y-axis is a risk metric — most commonly the **cumulative default rate** or the **30+ day delinquency rate**. Each line on the chart is one origination month.

The curve’s shape tells you whether newer loans are performing worse than older loans at the same age, which is a leading indicator of credit quality. If the April vintage is at 2% default by MOB 6 and the October vintage is at 4% default by MOB 6, something changed in underwriting between those months — and the credit officer wants to know before month 12.

If you measure defaults by calendar month, a spike in March 2026 could mean (a) something happened in March 2026 (macro shock) or (b) a lot of high-risk loans happened to be 6 months old in March 2026 (vintage effect). Vintage curves isolate (b) by grouping loans by origination date, so calendar-month noise doesn’t contaminate the signal.

Why vintage, not calendar?

The core move is joining originations to the status history on `months_on_book` rather than on calendar date. Every warehouse has slightly different date arithmetic — in PostgreSQL:

WITH originations AS (
  SELECT
    loan_id,
    DATE_TRUNC('month', originated_at)::date AS vintage_month
  FROM fact_originations
),
loan_ages AS (
  SELECT
    o.vintage_month,
    o.loan_id,
    s.status,
    (EXTRACT(YEAR  FROM s.status_date) - EXTRACT(YEAR  FROM o.vintage_month)) * 12
      + (EXTRACT(MONTH FROM s.status_date) - EXTRACT(MONTH FROM o.vintage_month)) AS months_on_book
  FROM originations o
  JOIN fact_loan_status s USING (loan_id)
)
SELECT
  vintage_month,
  months_on_book,
  COUNT(DISTINCT loan_id)                                                       AS loans,
  SUM(CASE WHEN status = '30_day_delinquent' THEN 1 ELSE 0 END)                 AS dq_30,
  ROUND(100.0 * SUM(CASE WHEN status = '30_day_delinquent' THEN 1 ELSE 0 END)
                / NULLIF(COUNT(DISTINCT loan_id), 0), 2)                        AS dq_rate_pct
FROM loan_ages
WHERE months_on_book BETWEEN 1 AND 24
GROUP BY vintage_month, months_on_book
ORDER BY vintage_month, months_on_book;

Three details to notice. First, the `DATE_TRUNC` rolls originations up to the month level — daily vintages are too granular to read in a chart. Second, the `months_on_book` math uses year+month arithmetic rather than `AGE()` or `EXTRACT(DAYS)` because stakeholders expect MOB 12 to mean "exactly one year later," not "365 or 366 days later depending on the leap year." Third, `NULLIF(..., 0)` on the denominator so a vintage with zero loans doesn’t blow up the query.

Where vintage curves answer "how are loans performing as they age," roll rates answer "what percent of loans currently in delinquency bucket X will move to bucket X+1 next month." The matrix has delinquency buckets on both axes — Current, 30-DQ, 60-DQ, 90-DQ, 120+-DQ — and each cell is the probability of transitioning from the row state this month to the column state next month.

Credit officers use roll rates to forecast losses. If 45% of 60-DQ loans historically roll to 90-DQ, and 80% of 90-DQ loans eventually charge off, then the 60-DQ population tells you roughly how many dollars will charge off in two months. That forecast lands in the loss reserve (allowance for credit losses) on the balance sheet.

WITH status_pairs AS (
  SELECT
    curr.loan_id,
    curr.status AS from_status,
    next.status AS to_status
  FROM fact_loan_status curr
  JOIN fact_loan_status next
    ON next.loan_id = curr.loan_id
   AND next.status_date = (curr.status_date + INTERVAL '1 month')::date
)
SELECT
  from_status,
  to_status,
  COUNT(*)                                                   AS transitions,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY from_status), 2) AS pct_of_from
FROM status_pairs
GROUP BY from_status, to_status
ORDER BY from_status, to_status;

The `SUM(COUNT(*)) OVER (PARTITION BY from_status)` is the trick — an analytic function returning the total transitions out of each `from_status` bucket, which becomes the denominator for the percentage. This is one of those queries where window functions earn their complexity.

The classic follow-up once roll rates are in hand: **net charge-off (NCO) rate**. Charge-offs minus recoveries, divided by average outstanding balance. Annualized. Stakeholders want it monthly, by product, sometimes by vintage. The query is short but every piece has a reason:

SELECT
  DATE_TRUNC('month', charge_off_date)::date AS month,
  product,
  SUM(charge_off_amount)         AS gross_co,
  SUM(recovery_amount)           AS recoveries,
  SUM(charge_off_amount) - SUM(recovery_amount) AS net_co,
  AVG(outstanding_balance)       AS avg_balance,
  ROUND(
    12 * 100.0 * (SUM(charge_off_amount) - SUM(recovery_amount))
         / NULLIF(AVG(outstanding_balance), 0),
    2
  ) AS nco_rate_annualized_pct
FROM fact_loan_status
WHERE charge_off_date IS NOT NULL
GROUP BY DATE_TRUNC('month', charge_off_date), product
ORDER BY month, product;

Monthly NCO rates are multiplied by 12 to annualize for comparability with reported figures (which are usually annual). If a stakeholder asks for the "raw" or "monthly" rate, drop the 12x. If they ask for "annualized" and you give them monthly, they will look at the number, compare it to the 10-K annual, and think credit quality dropped 12x. It won’t go well.

The 12-multiplier annualization

Risk-analyst interviews at banks and fintechs almost always include a vintage-curve question in some form. Common phrasings: "how would you detect that credit quality is deteriorating?" (answer: vintage curves), "how would you forecast losses for next quarter?" (answer: roll rates), "walk me through building an allowance model" (answer: roll-rate-to-NCO chained together). Interviewers rarely want perfect SQL; they want to see that you understand why the shape of the data matters.

A few signal-sending moves during the interview: mention that you’d exclude the first and last MOB of each vintage from cross-vintage comparisons (edge effects); mention that roll rates drift with the macro cycle and point estimates from one quarter can be misleading in a recession; mention that NCO isolates the realized loss but provision-to-NCO gaps can signal management’s view on the cycle. Each of those is one sentence that makes you sound like a year-two analyst, not a year-zero one.

None of this is exotic SQL — it’s `CASE WHEN`, window functions, and careful NULL handling. What makes it senior work is the business fluency on top: knowing which buckets matter, why the macro overlay exists, how loss reserves read on the balance sheet. That’s the bar caseSQL’s banking path is written to.

Build monthly vintage curves and roll-rate matrices in SQL — the queries credit-risk analysts at banks and lenders write every week. Runnable exercises against a banking schema.

Finance

Vintage Curves, Roll Rates, and the SQL Credit-Risk Analysts Write