Delinquency Aging Buckets (30/60/90 Days Past Due)

WITH classified_loans AS (
        SELECT 
          loan_id,
          unpaid_principal,
          days_overdue,
          CASE 
            WHEN days_overdue >= 90 THEN '90+ DPD'
            WHEN days_overdue >= 60 THEN '60-89 DPD'
            WHEN days_overdue >= 30 THEN '30-59 DPD'
            ELSE '1-29 DPD'
          END AS aging_bucket
        FROM consumer_loans
        WHERE days_overdue >= 1
      )
      SELECT 
        aging_bucket,
        COUNT(*) AS loan_count,
        SUM(unpaid_principal) AS total_unpaid_principal
      FROM classified_loans
      GROUP BY aging_bucket
      ORDER BY total_unpaid_principal DESC;