Coding questions · Whatnot

Compute a Seven-Day Rolling Average

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.

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.