Time Intelligence — Rolling, Cumulative, and Period Queries

Time intelligence is the ability to compare metrics across time periods — not just what happened, but how it compares to before. These are among the most-requested analyses in any business.

The Three Core Time Patterns

1. Rolling windows — moving average, rolling sum (smooths noise)
2. Cumulative totals — running sum from start to now (shows growth)
3. Period comparisons — this period vs last period (shows trend direction)

Example 1: All Three Patterns Together

WITH daily_revenue AS (
    SELECT DATE_TRUNC('day', o.created_at)::DATE AS day,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM ec_orders o JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1
)
SELECT day, ROUND(revenue, 2) AS daily_revenue,
    -- Rolling 7-day average
    ROUND(AVG(revenue) OVER (ORDER BY day ROWS 6 PRECEDING), 2) AS rolling_7d_avg,
    -- Rolling 30-day sum
    ROUND(SUM(revenue) OVER (ORDER BY day ROWS 29 PRECEDING), 2) AS rolling_30d_total,
    -- Cumulative from start
    ROUND(SUM(revenue) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING), 2) AS cumulative_total,
    -- Day over day change
    ROUND(revenue - LAG(revenue) OVER (ORDER BY day), 2) AS dod_change,
    -- 7-day ago comparison
    ROUND(revenue - LAG(revenue, 7) OVER (ORDER BY day), 2) AS wow_change
FROM daily_revenue
ORDER BY day;

Example 2: Year-to-Date vs Prior Year YTD

WITH monthly_rev AS (
    SELECT EXTRACT(YEAR FROM o.created_at)::INT AS year,
        EXTRACT(MONTH FROM o.created_at)::INT AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM ec_orders o JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1, 2
),
ytd_rev AS (
    SELECT year, month, revenue,
        SUM(revenue) OVER (PARTITION BY year ORDER BY month) AS ytd_revenue
    FROM monthly_rev
)
SELECT year, month, ROUND(revenue, 2) AS monthly_revenue,
    ROUND(ytd_revenue, 2) AS ytd_revenue,
    ROUND(LAG(ytd_revenue) OVER (PARTITION BY month ORDER BY year), 2) AS prior_ytd,
    ROUND(100.0 * (ytd_revenue - LAG(ytd_revenue) OVER (PARTITION BY month ORDER BY year))
        / NULLIF(LAG(ytd_revenue) OVER (PARTITION BY month ORDER BY year), 0), 1) AS ytd_growth_pct
FROM ytd_rev ORDER BY year, month;

Example 3: Trailing Twelve Months (TTM)

WITH monthly_rev AS (
    SELECT DATE_TRUNC('month', o.created_at)::DATE AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM ec_orders o JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1
)
SELECT month, ROUND(revenue, 2) AS monthly_revenue,
    ROUND(SUM(revenue) OVER (ORDER BY month ROWS 11 PRECEDING), 2) AS ttm_revenue,
    COUNT(*) OVER (ORDER BY month ROWS 11 PRECEDING) AS months_in_ttm
FROM monthly_rev
WHERE (SELECT COUNT(*) FROM monthly_rev mr2 WHERE mr2.month <= monthly_rev.month) >= 12
ORDER BY month;

Example 4: Week-over-Week with Calendar Alignment

WITH weekly AS (
    SELECT DATE_TRUNC('week', o.created_at)::DATE AS week,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        COUNT(DISTINCT o.order_id) AS ec_orders
    FROM ec_orders o JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1
)
SELECT week, ROUND(revenue, 2) AS revenue, ec_orders,
    ROUND(LAG(revenue) OVER (ORDER BY week), 2) AS prev_week,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week)) / NULLIF(LAG(revenue) OVER (ORDER BY week), 0), 1) AS wow_pct,
    ROUND(AVG(revenue) OVER (ORDER BY week ROWS 3 PRECEDING), 2) AS rolling_4w_avg
FROM weekly ORDER BY week DESC LIMIT 12;