Gap-Filling Time Series

Gap-Filling Sparse Time Series with generate_series

GROUP BY day returns only days that have rows, so charts and moving averages over a small market silently skip empty days. Left-joining a generate_series calendar restores them as zeros:

Daily New Zealand web orders with the empty days filled inSQL

WITH sparse AS (                       -- New Zealand's web orders: some days have none
  SELECT (o.order_ts AT TIME ZONE 'UTC')::date AS day, count(*) AS orders
  FROM orders o JOIN customers c USING (customer_id)
  WHERE c.country = 'NZ' AND o.channel = 'web'
  GROUP BY 1
)
SELECT d::date AS day, s.orders AS raw, coalesce(s.orders, 0) AS orders,
       round(avg(coalesce(s.orders, 0)) OVER (ORDER BY d ROWS 2 PRECEDING), 2) AS avg_3d
FROM generate_series(date '2026-06-01', date '2026-06-30', interval '1 day') AS d
LEFT JOIN sparse s ON s.day = d
ORDER BY d LIMIT 5;

Output

    day     | raw | orders | avg_3d
------------+-----+--------+--------
 2026-06-01 |   1 |      1 |   1.00
 2026-06-02 |     |      0 |   0.50
 2026-06-03 |     |      0 |   0.33
 2026-06-04 |   1 |      1 |   0.33
 2026-06-05 |   1 |      1 |   0.67

raw is what GROUP BY alone gives, with 2 and 3 June missing. Over the filled series a ROWS frame really is three days, so avg_3d on 4 June is 0.33; over the raw rows it would treat 4 June, 1 June and the last May order day as consecutive. Fill gaps before moving averages or LAG, and start the series early when edges matter.

MENU