Compute a Seven-Day Rolling Average
Company: Whatnot
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
# Compute a Seven-Day Rolling Average
Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.
## Schema
daily_metrics
| column | type | description |
|---|---|---|
| metric_date | date | Unique calendar date |
| metric_value | numeric | Observed daily value; may be NULL |
## Task
For each date, compute the average of non-NULL metric_value values over the current date and previous six calendar days. Dates are unique but the table may have gaps. The window is calendar-based, so a missing date contributes no row rather than shifting the window to an older observation.
## Required Output
Return metric_date and rolling_7d_avg rounded to two decimal places. Include every input date and sort ascending.
## Constraints
- Ignore NULL metric values in the average.
- Do not treat the last seven rows as the last seven calendar days.
- The first six dates use whatever observations exist inside their shorter calendar window.
```hint Use a date range frame
A correlated date predicate or an interval-based window preserves calendar semantics when dates are missing.
```
Overview: A PostgreSQL interview problem about computing a seven-day rolling average from dated observations. Candidates must use window frames carefully, preserve the requested grain, handle incomplete history, and return deterministically ordered results.
For every input date, compute the average of non-NULL metric_value observations from that date and the previous six calendar days, round it to two decimal places, and return metric_date and rolling_7d_avg ordered by date ascending. Dates are unique and may have gaps; missing dates contribute no row and must not extend the window to older observations.
Tables
daily_metrics(metric_date DATE, metric_value NUMERIC)
Hints
- Use the current date and its six-day calendar lookback rather than counting the previous six rows.