Write SQL for rolling default rates
Company: Citibank
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Write a SQL query to compute 12‑month rolling default rates by customer segment. Assume table loans(month DATE, segment TEXT, defaults INT, accounts INT). For each segment and month, compute SUM(defaults) over the current and prior 11 months divided by SUM(accounts) over the same window.
Quick Answer: This question evaluates proficiency with SQL window functions and time-series aggregation, specifically the ability to compute rolling metrics across customer segments by aggregating defaults and accounts over a moving 12-month window.