Capital One·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Capital One Data Scientist interview with a pretty gnarly SQL question that combined data cleaning, window functions, and multi-part output into one big query. No behavioral stuff from what I can tell, just pure technical depth.

Questions Asked (1)

You're given four CSVs (platforms, ads, videos, and a daily totals table) where the date column uses dashes to mean 'same as the row above.' Using only SQL (no procedural code), forward-fill those dates, then for a 7-day window compute per-ad and per-platform aggregates including CTR, and return both the top 3 ads by plays and the peak watch-time date per ad, all in a single query.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures

Author's notes

This thing had like five sub-problems stapled together and they wanted one query.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into three logical stages: first, forward-fill the dates using a window function like LAST_VALUE with IGNORE NULLS; second, join the tables and compute the 7-day rolling aggregates per ad and per platform; third, use window functions to rank ads by plays and find the peak watch-time date per ad. Then combine the results into a single query using CTEs and UNION ALL or a final SELECT with conditional logic.

Pro tip: Mention that forward-filling with LAST_VALUE IGNORE NULLS is not universally supported (e.g., not in MySQL), so you might need a self-join or correlated subquery alternative—showing awareness of dialect differences demonstrates maturity. Also, clarify that the 7-day window should be defined as a rolling window (e.g., ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and that CTR should be computed as clicks/impressions, handling division by zero.

1. Forward-fill missing dates

Use a window function such as LAST_VALUE(date_col IGNORE NULLS) OVER (ORDER BY row_id) to propagate the last non-null date downward. If the SQL dialect doesn't support IGNORE NULLS, use a self-join or correlated subquery to find the most recent non-null date.

2. Join tables and compute 7-day rolling aggregates

Join the forward-filled tables on date and relevant keys (ad_id, platform_id). Then compute rolling sums of plays, impressions, clicks, and watch_time over a 7-day window per ad and per platform using window functions with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

3. Calculate CTR and other metrics

Compute CTR as clicks divided by impressions (with NULLIF to avoid division by zero). Also compute any other required aggregates like total plays or average watch time per ad and per platform.

4. Rank ads and find peak watch-time date

Use ROW_NUMBER() or RANK() to order ads by total plays within the 7-day window and select the top 3. For each ad, find the date with the maximum watch time using a window function like ROW_NUMBER() OVER (PARTITION BY ad_id ORDER BY watch_time DESC).

5. Combine results into a single query

Use CTEs to structure the logic, then combine the top 3 ads and peak watch-time dates using UNION ALL or a final SELECT with conditional columns. Ensure the output includes both per-ad and per-platform aggregates as required.

Key Points to Mention

Forward-filling with LAST_VALUE IGNORE NULLS and alternative approaches for dialects that don't support it.
Definition of the 7-day window as a rolling window (e.g., ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and whether it's per ad or per platform.
CTR calculation: clicks / impressions, with NULLIF to handle zero impressions.
Using window functions (ROW_NUMBER, RANK, SUM OVER) to compute rankings and aggregates without procedural code.
Handling multiple tables: joining on date and keys, and ensuring the forward-fill is applied before joins.
Structuring the final output to include both top 3 ads by plays and peak watch-time date per ad, possibly using UNION ALL or conditional aggregation.

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