Citibank·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL-heavy screen for a data scientist role at Citi. One question, pretty deep on window functions and financial metrics. Not a lot of fluff.

Questions Asked (1)

Write a SQL query to calculate 12-month rolling default rates by customer segment. You have a table with columns for month, segment, number of defaults, and number of accounts. For each segment and month, compute the sum of defaults over the current and prior 11 months divided by the sum of accounts over the same window.

Data ModelingProduct Analytics & Metrics

Author's notes

This one took me a minute to set up correctly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function with a ROWS BETWEEN frame to aggregate defaults and accounts over a 12-month rolling window partitioned by segment. Divide the windowed sum of defaults by the windowed sum of accounts to produce the rolling default rate, being careful to handle edge cases like insufficient history at the start of the series. Clearly alias columns and order results for readability.

Pro tip: In a banking context, mention that you would validate the query by checking that early months (with fewer than 12 prior periods) either return NULL or are explicitly flagged as partial-window estimates — regulators and risk teams care deeply about this distinction and it signals production-readiness awareness.

1. Clarify Table Structure & Assumptions

Confirm the table schema — month as a DATE or period key, segment as a categorical, defaults and accounts as integers — and ask whether months with no activity have explicit zero rows or are absent, since gaps affect window calculations.

2. Set Up the Window Function Frame

Use SUM() OVER (PARTITION BY segment ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) to aggregate both defaults and accounts across the rolling 12-month window for each segment independently.

3. Compute the Rolling Default Rate

Divide the windowed sum of defaults by the windowed sum of accounts, using NULLIF on the denominator to avoid division-by-zero errors, and multiply by 100.0 or cast appropriately to return a percentage or decimal rate.

4. Handle Partial Windows at Series Start

Add a COUNT(*) OVER the same window to detect months with fewer than 12 observations; either filter them out with a HAVING/WHERE clause or flag them with a CASE statement so consumers know the rate is based on incomplete history.

5. Finalize & Validate Output

Order results by segment and month, spot-check a single segment manually by summing a 12-month slice, and consider wrapping the window logic in a CTE for clarity and reusability in downstream queries.

Key Points to Mention

ROWS BETWEEN 11 PRECEDING AND CURRENT ROW vs. RANGE BETWEEN — ROWS is preferred here because it counts physical rows, not date-range intervals, ensuring exactly 12 data points when data is dense
PARTITION BY segment to ensure the rolling window resets per segment and does not bleed across segments
NULLIF(denominator, 0) to safely handle months where a segment has zero accounts and prevent runtime errors
Partial-window behavior at the beginning of the time series and how to communicate or filter incomplete rolling periods
Using a CTE (WITH clause) to separate the window aggregation from the final rate calculation, improving readability and maintainability
Potential need to pre-fill missing months with zero rows using a calendar/date spine join to ensure the 12-month window is contiguous

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.