Lesson Intermediate

Rolling Metrics & Period-over-Period

Executives don't ask "what was revenue on Tuesday?" They ask "how's revenue trending, and is it up or down vs last week?" The answer needs three things on one row per period:

The raw value.
A smoothed value (rolling average) to filter out day-to-day noise.
A comparison to the previous period: an absolute delta and a percent change.

The Window Functions lessons taught the syntax. This lesson is about wiring them together into the queries dashboards actually run.

The Canonical Shape #

sql

-- Daily paid revenue with 7-day smoothing and day-over-day change
WITH daily AS (
  SELECT
    order_date,
    ROUND(SUM(total), 2) AS daily_revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY order_date
)
SELECT
  order_date,
  daily_revenue,
  ROUND(
    AVG(daily_revenue) OVER (
      ORDER BY order_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    2
  ) AS rev_7d_avg,
  LAG(daily_revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
  ROUND(
    100.0 * (daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date))
    / NULLIF(LAG(daily_revenue) OVER (ORDER BY order_date), 0),
    1
  ) AS dod_pct_change
FROM daily
ORDER BY order_date;

Three windows on the same row: the rolling average (smoothed signal), the LAG (yesterday's value), and the percent-change formula built from both. The NULLIF(..., 0) guards against a zero-revenue denominator.

Week-over-Week Comparison #

For stable KPIs, week-over-week (WoW) is more useful than day-over-day because it cancels weekday seasonality. Bucket to the week first, then LAG by one week:

sql

WITH weekly AS (
  SELECT
    DATE_TRUNC('week', order_date) AS week_start,
    ROUND(SUM(total), 2) AS weekly_revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY week_start
)
SELECT
  week_start,
  weekly_revenue,
  LAG(weekly_revenue) OVER (ORDER BY week_start) AS prev_week_revenue,
  ROUND(
    100.0 * (weekly_revenue - LAG(weekly_revenue) OVER (ORDER BY week_start))
    / NULLIF(LAG(weekly_revenue) OVER (ORDER BY week_start), 0),
    1
  ) AS wow_pct_change
FROM weekly
ORDER BY week_start;

Same shape, different grain. Swap 'week' for 'month' to get MoM, or 'year' for YoY.

Warning

LAG is positional, not temporal.
If a week has zero revenue and simply doesn't appear in daily, LAG compares the current row to whatever the previous physical row is, which might be two weeks ago. For a true week-over-week KPI on sparse data, join against a date spine (a CTE of every calendar week in the period) and LEFT JOIN revenue onto it so missing weeks become explicit zero rows.

sql

WITH bounds AS (
  SELECT
    DATE_TRUNC('week', MIN(order_date)) AS first_week,
    DATE_TRUNC('week', MAX(order_date)) AS last_week
  FROM orders
),
weeks AS (
  SELECT UNNEST(generate_series(first_week, last_week, INTERVAL 1 WEEK)) AS week_start
  FROM bounds
)
-- then LEFT JOIN weekly revenue onto weeks
SELECT week_start FROM weeks;

(In DuckDB, generate_series in a SELECT returns one list value, and UNNEST expands it into rows. In Postgres, it's a set-returning function and the UNNEST is unnecessary.)

Most dashboards quietly get this wrong, and it shows up as phantom growth or drops that track holidays and outages, not the business.

Smoothed vs Raw for Trend Calls #

One rule of thumb for trend analysis: don't make claims from a single noisy day. If Monday is 20% up day-over-day, that's noise until you see the 7-day average also trending up. The rolling average is what stakeholders should react to; the raw daily is diagnostic.

sql

-- Compare rolling average today vs rolling average 7 days ago
WITH daily AS (
  SELECT order_date, SUM(total) AS daily_revenue
  FROM orders WHERE status = 'paid' GROUP BY order_date
),
smoothed AS (
  SELECT
    order_date,
    AVG(daily_revenue) OVER (
      ORDER BY order_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rev_7d_avg
  FROM daily
)
SELECT
  order_date,
  ROUND(rev_7d_avg, 2) AS rev_7d_avg,
  ROUND(LAG(rev_7d_avg, 7) OVER (ORDER BY order_date), 2) AS rev_7d_avg_prev_week,
  ROUND(
    100.0 * (rev_7d_avg - LAG(rev_7d_avg, 7) OVER (ORDER BY order_date))
    / NULLIF(LAG(rev_7d_avg, 7) OVER (ORDER BY order_date), 0),
    1
  ) AS smoothed_wow_pct
FROM smoothed
ORDER BY order_date;

Comparing smoothed-today to smoothed-7-days-ago is the single best noise-filtered trend metric for daily KPIs. The jittery daily chart stops mattering.

Try It #

Daily paid revenue with rolling average and day-over-day change. Try the week-over-week variant in the hints to feel how grain affects noise.

Loading SQL editor...

Practice #

Build a daily paid revenue report with order_date, daily_revenue, rev_7d_avg (rounded to 2), prev_day_revenue (LAG by 1 day), and dod_pct_change (day-over-day %, rounded to 1). First row has NULL for prev_day_revenue and dod_pct_change. No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Using LAG on sparse data and calling it "day over day": missing dates make LAG compare non-adjacent days. Densify against a date spine for temporal accuracy.
Reacting to a single noisy day. Look at the rolling average before making a call.
Forgetting NULLIF(..., 0) on the percent-change denominator. A zero-revenue prior period crashes the query.
Mixing grains. A 7-day rolling average on weekly data doesn't mean what you think. Match the rolling window to the bucket size.
Reporting absolute deltas for small numbers as if they were trends. Moving from 2 to 3 orders is a 50% increase, and probably noise.

Knowledge check #

4 questions

0 / 4 answered

A rolling 7-day average of daily values is implemented with:
For a rolling 30-day metric on data with gaps (some days missing), ROWS BETWEEN 29 PRECEDING AND CURRENT ROW:
For per-region rolling totals, add:
For "month-over-month % change," the right combination is:

Next Step #

You've finished the Analytics Patterns section. Next up: Performance & Modeling, covering indexes, normal forms, and explain plans, so the queries you've learned actually run fast at scale.