Rolling 30-Day Default Rate SQL | Dataford Interview Questions

Write a SQL query using window functions to calculate each Koalafi customer's rolling 30-day default rate by month.

Treat each row in customer_default_events as one customer obligation. A NULL defaulted value is not a default but remains part of the denominator. Include a customer-month when that customer has at least one event during the month.

Output

One row per customer and active calendar month, with customer_id, report_month, defaults_30_day, obligations_30_day, and rolling_30_day_default_rate
The rate is defaults divided by obligations in the 30-day period ending on the final day of the report month
Order by customer_id, then report_month ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Koalafi customer identifier
customer_name	VARCHAR(100)	Customer display name
state	VARCHAR(2)	Customer state abbreviation

customer_default_events

Column	Type	Description
event_idPK	INT	Unique obligation event identifier
customer_id	INT	Customer associated with the obligation
event_date	DATE	Date of the obligation event
defaulted	BOOLEAN	Whether the obligation defaulted
event_type	VARCHAR(30)	Type of obligation event

Your question is Rolling 30-Day Default Rate SQL. Start with the requirements and the two 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