rag/sample_templates/11_moving_average.sql

-- Template: Moving Average (Rolling Average)
-- Pattern: Calculate average over sliding time window
-- Use Case: Smooth out fluctuations, identify trends, 7-day MA, 30-day MA

SELECT 
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day,
    AVG(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as moving_avg_30day,
    daily_order_count,
    AVG(daily_order_count) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as avg_orders_7day
FROM (
    SELECT 
        DATE(order_date) as order_date,
        SUM(total_amount) as daily_revenue,
        COUNT(*) as daily_order_count
    FROM orders
    GROUP BY DATE(order_date)
) daily_stats
ORDER BY order_date;

-- Key Concepts:
-- - Window function with ROWS BETWEEN for sliding window
-- - N PRECEDING means previous N rows
-- - CURRENT ROW includes the current row
-- - For 7-day MA: use 6 PRECEDING (includes current = 7 total)
-- - Useful for smoothing volatile metrics