Business Problem

Klarna's credit risk team is reviewing how different loan vintages (loans grouped by the month they were originated) are performing, and they need each loan classified into a delinquency bucket — Current, 30 DPD, 60 DPD, or 90+ DPD/Default — based on how many consecutive payments it has currently missed in a row, not just whether it has ever missed a payment. A loan that missed one payment and then caught up should show as Current again, while a loan on its third consecutive missed payment needs to be flagged as a likely default, so the risk team can see which origination months are producing worse loans and tighten underwriting accordingly. Consumer lenders track loans by 'vintage' (the month they were originated) because loan quality varies significantly by when and under what underwriting standards a loan was written, and roll-rate analysis — tracking how loans move between delinquency buckets over time — is the standard way credit risk teams catch deteriorating underwriting or economic stress early. What matters for classification is the current consecutive missed-payment streak, not lifetime missed payments, since a borrower who caught up shouldn't be treated the same as one who is actively falling further behind.

Dataset & Schema

loans with loan_id, origination_date, loan_amount, monthly_installment; loan_payments with one row per scheduled installment per loan, including due_date, amount_due, amount_paid, and paid_date (NULL for missed payments).

CREATE TABLE loans (
  loan_id INTEGER PRIMARY KEY,
  origination_date DATE,
  loan_amount NUMERIC(10,2),
  monthly_installment NUMERIC(10,2)
);

CREATE TABLE loan_payments (
  payment_id INTEGER PRIMARY KEY,
  loan_id INTEGER,
  due_date DATE,
  amount_due NUMERIC(10,2),
  amount_paid NUMERIC(10,2),
  paid_date DATE,
  FOREIGN KEY (loan_id) REFERENCES loans(loan_id)
);

SQL Solution

WITH payment_flags AS (
  SELECT
    loan_id,
    due_date,
    amount_due,
    amount_paid,
    CASE WHEN amount_paid < amount_due THEN 1 ELSE 0 END AS is_missed,
    ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY due_date) AS payment_seq
  FROM loan_payments
),
islands AS (
  SELECT
    *,
    payment_seq - ROW_NUMBER() OVER (
      PARTITION BY loan_id, is_missed ORDER BY due_date
    ) AS streak_group
  FROM payment_flags
),
streaks AS (
  SELECT
    loan_id,
    is_missed,
    streak_group,
    COUNT(*) AS streak_length,
    MAX(due_date) AS streak_end_date
  FROM islands
  WHERE is_missed = 1
  GROUP BY loan_id, is_missed, streak_group
),
latest_payment AS (
  SELECT loan_id, MAX(due_date) AS last_due_date
  FROM payment_flags
  GROUP BY loan_id
),
current_streak AS (
  -- the streak that ends on (or includes) the loan's most recent due date, if it's a missed-payment streak
  SELECT s.loan_id, s.streak_length
  FROM streaks s
  JOIN latest_payment lp ON lp.loan_id = s.loan_id AND lp.last_due_date = s.streak_end_date
),
delinquency AS (
  SELECT
    l.loan_id,
    l.origination_date,
    DATE_TRUNC('month', l.origination_date)::date AS vintage_month,
    COALESCE(cs.streak_length, 0) AS consecutive_missed_payments,
    CASE
      WHEN COALESCE(cs.streak_length, 0) = 0 THEN 'Current'
      WHEN cs.streak_length = 1 THEN '30 DPD'
      WHEN cs.streak_length = 2 THEN '60 DPD'
      ELSE '90+ DPD / Default'
    END AS delinquency_bucket
  FROM loans l
  LEFT JOIN current_streak cs ON cs.loan_id = l.loan_id
)
SELECT
  TO_CHAR(vintage_month, 'YYYY-MM') AS vintage_month,
  delinquency_bucket,
  COUNT(*) AS loan_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY vintage_month), 1) AS pct_of_vintage
FROM delinquency
GROUP BY vintage_month, delinquency_bucket
ORDER BY vintage_month, delinquency_bucket;

Explanation