Your question is Pivot Rows Into Columns. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
The dv01 Performance Data surface receives monthly loan-level servicing records for securitization deals. Write a PostgreSQL query that produces one portfolio summary row per deal as of January 31, 2025.
loaded_at value.current_balance, dpd_30_balance, dpd_60_balance, and dpd_90_plus_balance columns based on delinquency_days.total_balance and the percentage of total balance that is 90 or more days delinquent.deal_id.dv01_deals contains deals D001 through D008. Active loans include loans 101 and 102 in D001, 103 and 104 in D002, and one active loan each in D003 through D006. D007 has only a paid-off loan, while D008 has no loans.
| Column | Type | Description |
|---|---|---|
| deal_idPK | VARCHAR(10) | Unique securitization deal identifier |
| deal_name | VARCHAR(100) | Display name for the deal |
| asset_class | VARCHAR(50) | Underlying asset category |
| Column | Type | Description |
|---|---|---|
| loan_idPK | INT | Unique loan identifier |
| deal_id | VARCHAR(10) | Deal containing the loan |
| loan_status | VARCHAR(20) | Loan lifecycle status |
| Column | Type | Description |
|---|---|---|
| loan_id | INT | Loan associated with the performance record |
| reporting_month | DATE | Month represented by the record |
| loaded_at | TIMESTAMP | Timestamp when the record was loaded |
| delinquency_days | INT | Days the loan is past due |
| principal_balance | NUMERIC(14,2) | Principal balance at the reporting date |