Fetch·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

Fetch Data Scientist interview with a SQL question focused on rolling averages over sparse time series data. The calendar-day window requirement is the part that trips people up.

Questions Asked (1)

Given a table of daily product metrics where some dates may be missing, write a SQL query using window functions to compute the 7-day rolling average of daily active users. The window must be based on calendar-day distance, not row count.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

The row-count vs calendar-day distinction is what makes this non-trivial.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, generate a complete date series to fill missing dates, then left join the daily metrics and replace nulls with zeros. Use a window function with RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW to compute the rolling average based on calendar days, ensuring the window covers exactly 7 days.

Pro tip: Mention that using ROWS would incorrectly count rows, so RANGE is essential for calendar-day windows. Also, clarify how to handle missing dates (e.g., zero-fill) and note that the average should be over 7 days, not just existing days.

1. Generate a complete date series

Create a date spine covering the entire period of interest to ensure no calendar days are missing. This can be done with a recursive CTE or a calendar table.

2. Join metrics and fill missing values

Left join the date spine to the daily metrics table and replace null DAU values with 0 (or leave as null if appropriate). This ensures every date has a value for the rolling calculation.

3. Compute rolling average with RANGE window

Use AVG(dau) OVER (ORDER BY date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) to calculate the 7-day rolling average based on calendar days, not row count.

4. Handle edge cases and validate

Consider how to handle the first few days where the window is incomplete (e.g., require full 7 days or allow partial). Validate results by checking a few dates manually.

Key Points to Mention

Difference between ROWS and RANGE in window functions
Importance of a date spine to fill missing dates
Handling nulls or missing data (e.g., zero-fill) before averaging
Syntax for RANGE with INTERVAL (e.g., RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
Edge cases: first days of data, incomplete windows, and whether to include them
Performance considerations for large datasets (e.g., indexing, partitioning)

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