Rolling Delinquency SQL | Dataford Interview Questions

Write a SQL query using window functions to calculate a rolling 30-day delinquency rate by customer segment at Credit Saison India.

Use the provided customer, loan, and daily loan status data. Treat a loan as delinquent when days_past_due >= 30. The rate should use observations from the current date and preceding 29 calendar days.

Output

One row per segment and status date with segment, status_date, rolling_delinquent_loans, rolling_observed_loans, and rolling_delinquency_rate.
Exclude records without a customer segment and order by segment, then status_date.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
segment	VARCHAR(30)	Customer risk or business segment

loans

Column	Type	Description
loan_idPK	INT	Unique loan identifier
customer_id	INT	Customer who owns the loan
product_type	VARCHAR(30)	Loan product category

loan_daily_status

Column	Type	Description
status_idPK	INT	Unique status observation identifier
loan_id	INT	Loan being measured
status_date	DATE	Date of the loan status observation
days_past_due	INT	Days the payment is overdue

Your question is Rolling Delinquency SQL. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem