Moving Average with Missing Days

Difficulty: Medium · Topics: moving-average, range-frame, date-spine · Asked at: Uber, Airbnb, Stripe

Problem

store_sales only has rows for days with sales. For every day from 2026-06-01 to 2026-06-10 (including days without sales), return day, revenue (0 when no sales) and ma_7d: the average daily revenue over the 7 calendar days ending on that day, counting missing days as 0 and only days on or after 2026-06-01. Round to 2 decimals. Order by day.

Schema and sample data

CREATE TABLE store_sales (day TEXT, revenue INTEGER);
INSERT INTO store_sales VALUES
('2026-06-01',70),('2026-06-02',140),('2026-06-05',210),('2026-06-06',70),('2026-06-09',350),('2026-06-10',70);

Expected output

Hints

Solution

WITH RECURSIVE days(day) AS (
  SELECT '2026-06-01'
  UNION ALL
  SELECT DATE(day, '+1 day') FROM days WHERE day < '2026-06-10'
), dense AS (
  SELECT d.day, COALESCE(SUM(s.revenue), 0) AS revenue
  FROM days d LEFT JOIN store_sales s ON s.day = d.day
  GROUP BY d.day
)
SELECT day, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma_7d
FROM dense
ORDER BY day;

Alternative 1

WITH RECURSIVE days(day) AS (
  SELECT '2026-06-01' UNION ALL SELECT DATE(day, '+1 day') FROM days WHERE day < '2026-06-10'
), dense AS (
  SELECT d.day, COALESCE(SUM(s.revenue), 0) AS revenue, CAST(julianday(d.day) AS INTEGER) AS dnum
  FROM days d LEFT JOIN store_sales s ON s.day = d.day GROUP BY d.day
)
SELECT day, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY dnum RANGE BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma_7d
FROM dense ORDER BY day;

Explanation

Two classic approaches:

1. Densify (date spine + LEFT JOIN + COALESCE 0), then ROWS. Works in every engine, and the dense table is often useful anyway (charts with zeros).
2. RANGE frame on a numeric day number (RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) on the sparse table. Note this averages only the days that exist in the frame, i.e. it ignores missing days rather than treating them as 0. That’s a different (and usually wrong) metric. Clarify which one the interviewer wants!

In early June the frame contains fewer than 7 days because history before 06-01 is out of scope, so the first rows average 1..6 days.

Follow-up questions

Dialect notes

Spark: build the spine with sequence(to_date(start), to_date(end), interval 1 day) + explode. Postgres: generate_series(date, date, interval '1 day'). In production, join to a dim_date table.