Monthly Delinquency Snapshot: Bucketing Loan Risk

Business Context

NorthBridge Consumer Finance is a mid-sized non-banking financial company (NBFC) headquartered in Chicago, managing a retail loan portfolio of approximately 4,800 active loan accounts across five product lines — Personal Loans, Auto Loans, Home Loans, Business Loans, and Education Loans. The company operates across 8 regional branches and books roughly $18M in new disbursements each month. Every month, the Risk & Collections team produces a Delinquency Bucket Report — a portfolio-wide snapshot that classifies every active loan by how many days past due (DPD) its most recent payment is. This report feeds directly into the monthly risk committee meeting and informs provisioning decisions under IFRS 9 expected credit loss (ECL) norms.

For November 2024, the Head of Risk has asked for the delinquency snapshot to be generated from the core loan management system's database. The report must segment all active loans into five regulatory buckets: Current (0 DPD), Bucket 1 (1–30 DPD), Bucket 2 (31–60 DPD), Bucket 3 (61–90 DPD), and Bucket 4 (90+ DPD) — showing total loan count and total outstanding balance per bucket.

The SQL Challenge

The loan performance table stores raw days_past_due values as integers — there is no pre-labeled bucket column. You will need to use a CASE statement to programmatically assign each loan to its bucket category, a date filter on report_month to isolate the November 2024 snapshot, a loan_status filter to exclude closed and written-off accounts, and GROUP BY with aggregate functions (COUNT and SUM) to roll up loan counts and outstanding balances per bucket. A JOIN to the loans table brings in product-level context.

Your Role and Deliverable

You are the Data Analyst on the Advanced Analytics team at NorthBridge. The Risk Manager has pinged you on Slack asking for the November 2024 delinquency breakdown before the 3 PM risk committee call. Your job is to write a single SQL query that reads from the loan_performance and loans tables, applies the bucket classification logic, and returns one clean result set — five rows, one per bucket — showing delinquency_bucket, loan_count, total_outstanding_balance, and avg_days_past_due. This output will be pasted directly into the risk committee slide deck.