Multi-CTE cohort definition + 2-dim GROUP BY pivot to surface monotonic status progression
Roll-progression view for the monthly servicing review. For every loan that ever missed a payment, find the month of the first miss, then bucket against the loan's `current_status` today. Three columns: `first_miss_month` ('YYYY-MM'), `current_status`, `loan_count`. Filter `first_miss_date >= '2024-11-01'` to skip the 2019 cohort. Sort by first_miss_month ascending, then current_status ascending.
| Column | Type | Key |
|---|---|---|
| origination_id | INT | PK |
| app_id | INT | FK → lending_applications |
| account_id | INT | FK → retail_accounts |
| funded_date | TEXT | |
| funded_amount | REAL | |
| term_months | INT | |
| interest_rate | REAL | |
| rate_type | TEXT | |
| arm_reset_date | TEXT | |
| fico_at_orig | INT | |
| dti_at_orig | REAL | |
| ltv_at_orig | REAL | |
| property_value | REAL | |
| lo_name | TEXT | |
| channel | TEXT | |
| current_status | TEXT | |
| closed_date | TEXT |
| Column | Type | Key |
|---|---|---|
| payment_id | INT | PK |
| origination_id | INT | FK → lending_originations |
| due_date | TEXT | |
| paid_date | TEXT | |
| scheduled_amount | REAL | |
| principal_paid | REAL | |
| interest_paid | REAL | |
| escrow_paid | REAL | |
| days_late | INT | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
First CTE: per origination, find the date of the first miss using a MIN aggregate filtered to the missed status. One row per loan that ever missed.
Outer SELECT: JOIN that CTE back to `lending_originations` so each loan's `current_status` rides along. GROUP BY two dimensions — bucketed first-miss month (DATE_TRUNC + TO_CHAR) and current_status — and COUNT.
Two-key sort (month asc, status asc) gives the chronological reading order plus a deterministic tie-break when two months happen to have the same set of statuses present.