Write SQL for rolling default rates
Company: Wells Fargo
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.
Overview: 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.
Read the full Wells Fargo Data Scientist interview experience this question came from
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)
Tables
loans(month DATE, segment VARCHAR(50), defaults INT, accounts INT)
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.