Coding questions · Wells Fargo
Write SQL for rolling default rates
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.
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.
Compute 12-Month Rolling Default Rates by Segment
You are given a loans table with monthly data by customer segment:
- month: the calendar month of the data
- segment: customer segment label
- defaults: number of defaulted accounts in that month
- accounts: number of active accounts in that month
Write a SQL query to compute, for each segment and month, the 12-month rolling default rate. For a given segment and month, the rolling default rate is defined as:
SUM(defaults) over the current month and prior 11 months
------------------------------------------------------
SUM(accounts) over the current month and prior 11 months
For months where there are fewer than 11 prior months of data, use all available prior months for that segment. Return one row per segment and month with the following columns:
- month
- segment
- rolling_defaults (12-month window sum of defaults)
- rolling_accounts (12-month window sum of accounts)
- rolling_default_rate (rolling_defaults / rolling_accounts, rounded to 4 decimal places)
Table loans
| column | type | constraints |
|---|---|---|
| month | DATE | NOT NULL |
| segment | VARCHAR(50) | NOT NULL |
| defaults | INT | NOT NULL |
| accounts | INT | NOT NULL |
Sample data:
| accounts | defaults | month | segment |
|---|---|---|---|
| 100 | 2 | 2023-01-01 | Retail |
| 100 | 3 | 2023-02-01 | Retail |
| 100 | 1 | 2023-03-01 | Retail |
| 100 | 4 | 2023-04-01 | Retail |
| 100 | 2 | 2023-05-01 | Retail |
| 100 | 3 | 2023-06-01 | Retail |
| 100 | 2 | 2023-07-01 | Retail |
| 100 | 5 | 2023-08-01 | Retail |
| 100 | 1 | 2023-09-01 | Retail |
| 100 | 2 | 2023-10-01 | Retail |
| 100 | 3 | 2023-11-01 | Retail |
| 100 | 4 | 2023-12-01 | Retail |
| 100 | 2 | 2024-01-01 | Retail |
| 100 | 3 | 2024-02-01 | Retail |
| 50 | 1 | 2023-01-01 | Corporate |
| 50 | 2 | 2023-02-01 | Corporate |
| 50 | 1 | 2023-03-01 | Corporate |
| 50 | 1 | 2023-04-01 | Corporate |
| 50 | 2 | 2023-05-01 | Corporate |
| 50 | 2 | 2023-06-01 | Corporate |
| 50 | 3 | 2023-07-01 | Corporate |
| 50 | 1 | 2023-08-01 | Corporate |
| 50 | 2 | 2023-09-01 | Corporate |
| 50 | 1 | 2023-10-01 | Corporate |
| 50 | 2 | 2023-11-01 | Corporate |
| 50 | 1 | 2023-12-01 | Corporate |
| 50 | 2 | 2024-01-01 | Corporate |
| 50 | 1 | 2024-02-01 | Corporate |
Expected output
One row per segment and month, showing the 12-month (or shorter at the beginning) rolling sums of defaults and accounts, and the corresponding rolling default rate rounded to 4 decimal places.
| month | rolling_accounts | rolling_default_rate | rolling_defaults | segment |
|---|---|---|---|---|
| 2023-01-01 | 50 | 0.02 | 1 | Corporate |
| 2023-02-01 | 100 | 0.03 | 3 | Corporate |
| 2023-03-01 | 150 | 0.0267 | 4 | Corporate |
| 2023-04-01 | 200 | 0.025 | 5 | Corporate |
| 2023-05-01 | 250 | 0.028 | 7 | Corporate |
| 2023-06-01 | 300 | 0.03 | 9 | Corporate |
| 2023-07-01 | 350 | 0.0343 | 12 | Corporate |
| 2023-08-01 | 400 | 0.0325 | 13 | Corporate |
| 2023-09-01 | 450 | 0.0333 | 15 | Corporate |
| 2023-10-01 | 500 | 0.032 | 16 | Corporate |
| 2023-11-01 | 550 | 0.0327 | 18 | Corporate |
| 2023-12-01 | 600 | 0.0317 | 19 | Corporate |
| 2024-01-01 | 600 | 0.0333 | 20 | Corporate |
| 2024-02-01 | 600 | 0.0317 | 19 | Corporate |
| 2023-01-01 | 100 | 0.02 | 2 | Retail |
| 2023-02-01 | 200 | 0.025 | 5 | Retail |
| 2023-03-01 | 300 | 0.02 | 6 | Retail |
| 2023-04-01 | 400 | 0.025 | 10 | Retail |
| 2023-05-01 | 500 | 0.024 | 12 | Retail |
| 2023-06-01 | 600 | 0.025 | 15 | Retail |
| 2023-07-01 | 700 | 0.0243 | 17 | Retail |
| 2023-08-01 | 800 | 0.0275 | 22 | Retail |
| 2023-09-01 | 900 | 0.0256 | 23 | Retail |
| 2023-10-01 | 1000 | 0.025 | 25 | Retail |
| 2023-11-01 | 1100 | 0.0255 | 28 | Retail |
| 2023-12-01 | 1200 | 0.0267 | 32 | Retail |
| 2024-01-01 | 1200 | 0.0267 | 32 | Retail |
| 2024-02-01 | 1200 | 0.0267 | 32 | Retail |
Hints
- Use a window function with PARTITION BY segment and ORDER BY month.
- Define a window frame of ROWS BETWEEN 11 PRECEDING AND CURRENT ROW to capture a 12-month rolling window.