Part 180: Time-Series Gap Filling, Interpolation & Rolling Volatility

Filling missing dates in metrics streams and calculating rolling financial metrics.

1. The Missing Timestamp Problem

If no sales occurred on Wednesday, a simple GROUP BY sale_date completely skips Wednesday, creating broken charts.

2. Generating Continuous Calendar Grids

-- Generate continuous daily sequence from 2026-01-01 to 2026-01-31
WITH calendar AS (
    SELECT generate_series(
        '2026-01-01'::date, 
        '2026-01-31'::date, 
        '1 day'::interval
    )::date AS calendar_date
)
SELECT 
    c.calendar_date,
    COALESCE(SUM(o.amount), 0.00) AS daily_revenue,
    COUNT(o.id) AS transaction_count
FROM calendar c
LEFT JOIN orders o ON c.calendar_date = DATE(o.created_at)
GROUP BY c.calendar_date
ORDER BY c.calendar_date ASC;

3. Forward Fill (LOCF: Last Observation Carried Forward)

When a sensor or stock price has missing readings, carry the last known reading forward:

SELECT 
    calendar_date,
    price,
    -- Carry forward previous non-null value
    COALESCE(price, LAG(price) OVER (ORDER BY calendar_date)) AS locf_price
FROM daily_stock_prices;

4. Rolling Standard Deviation (Volatility)

SELECT 
    ticker,
    trade_date,
    close_price,
    -- 30-day rolling standard deviation of closing price
    STDDEV(close_price) OVER (
        PARTITION BY ticker 
        ORDER BY trade_date 
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS rolling_30d_volatility
FROM stock_history;

Key Takeaways

• Always anchor time-series reporting against a calendar grid generated via generate_series() or an enterprise DimDate table.

In Part 181, we enter Module 13: Advanced Data Modeling, Security & Compliance, starting with Slowly Changing Dimensions (SCD Types 1, 2, 3, 4 & 6)!