Katkat
Back

Compute a Seven-Day Rolling Average

WhatnotData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

Compute a Seven-Day Rolling Average

Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.

Schema

daily_metrics

columntypedescription
metric_datedateUnique calendar date
metric_valuenumericObserved 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.

Hints

Hint 1

Use a date range frame: A correlated date predicate or an interval-based window preserves calendar semantics when dates are missing.