rag/sample_templates/04_running_totals.sql

-- Template: Running Totals and Cumulative Sums
-- Pattern: Calculate cumulative values over ordered records
-- Use Case: Running balance, cumulative revenue, YTD totals

SELECT 
    order_date,
    customer_id,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    SUM(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as customer_running_total,
    AVG(total_amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day
FROM orders
ORDER BY order_date;

-- Key Concepts:
-- - SUM() OVER (): Running total across all rows
-- - PARTITION BY: Separate running totals per customer
-- - ROWS BETWEEN: Define window frame
--   * UNBOUNDED PRECEDING: From start
--   * CURRENT ROW: Up to current row
--   * N PRECEDING: Previous N rows
-- - Useful for trends, YTD calculations, moving averages