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.
Requirements
- Retain every deal, including deals with no active loans or no performance record as of the reporting date.
- Use only the latest performance record for each active loan on or before January 31, 2025. Resolve duplicate records by choosing the greatest
loaded_at value.
- Pivot loan balances into
current_balance, dpd_30_balance, dpd_60_balance, and dpd_90_plus_balance columns based on delinquency_days.
- Calculate
total_balance and the percentage of total balance that is 90 or more days delinquent.
- Return results ordered by
deal_id.
Representative Sample Data
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.
