How to Calculate Moving Averages and Rolling Metrics in SQL

Daily revenue jumps 40% on Tuesday and drops 30% on Wednesday. Is that a trend or just noise? You already know the answer — it's noise. But your stakeholders don't, and a line chart of raw daily numbers invites exactly the wrong kind of panic.

Moving averages fix this. A 7-day MA smooths out day-of-week effects. A 28-day MA shows the actual monthly trend. A 30-day rolling sum gives you a run-rate you can compare month over month without waiting for the month to end. Standard window functions, five minutes to get right — except for the two or three gotchas that take an hour.

All examples use daily_metrics(date, revenue, orders, active_users) and events(user_id, event_type, created_at). Postgres syntax, but the window-function parts work identically on BigQuery, Snowflake, and Redshift.

7-day moving average and 30-day rolling sum

select
  date,
  revenue,
  round(avg(revenue) over (
    order by date
    rows between 6 preceding and current row
  ), 2) as revenue_7d_ma,
  sum(revenue) over (
    order by date
    rows between 29 preceding and current row
  ) as revenue_30d_rolling,
  round(avg(orders) over (
    order by date
    rows between 6 preceding and current row
  ), 1) as orders_7d_ma
from daily_metrics
where date >= current_date - interval '90 days'
order by date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "this row plus the six before it" — seven rows total. Not six. Off-by-one errors here are extremely common: if you write 7 PRECEDING, you get an 8-day window. Count on your fingers if you have to.

The date gap problem

Here's where it gets weird. That query assumes daily_metrics has a row for every single day. If your ETL skips weekends, or you simply had zero revenue on a Tuesday and didn't insert a row, ROWS BETWEEN 6 PRECEDING grabs six rows, not six days. Your "7-day" average might actually span two weeks.

Fix this by generating a complete date spine first:

with date_spine as (
  select generate_series(
    current_date - interval '90 days',
    current_date,
    interval '1 day'
  )::date as date
),
filled as (
  select
    ds.date,
    coalesce(dm.revenue, 0) as revenue,
    coalesce(dm.orders, 0) as orders,
    coalesce(dm.active_users, 0) as active_users
  from date_spine ds
  left join daily_metrics dm on dm.date = ds.date
)
select
  date,
  revenue,
  round(avg(revenue) over (
    order by date rows between 6 preceding and current row
  ), 2) as revenue_7d_ma,
  sum(revenue) over (
    order by date rows between 29 preceding and current row
  ) as revenue_30d_rolling
from filled
order by date;

Skip the date spine and every downstream rolling metric is silently wrong whenever a day is missing. I've seen dashboards that looked correct for months until someone noticed the "7-day average" was actually a 9-day average because two weekends were missing from the source table.

ROWS vs RANGE — when it matters

ROWS BETWEEN counts physical rows. RANGE BETWEEN counts by the value in the ORDER BY column — so RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW looks at actual dates, not row positions. If your data has no gaps, they produce identical results. If your data has gaps, RANGE won't accidentally include data from two weeks ago just because there were only seven rows in that span.

The catch: RANGE with interval offsets works on Postgres and BigQuery but not everywhere. Redshift doesn't support it (as of mid-2026). MySQL added it in 8.0 but with quirks. If you're writing portable SQL, fill the gaps and use ROWS. If you're on Postgres only, RANGE is elegant but the date spine approach is more explicit about what's happening.

Exponential moving average in SQL

A simple MA weights every day equally. An EMA weights recent data more heavily — useful for metrics where yesterday matters more than last Tuesday. SQL doesn't have a built-in EMA, but a recursive CTE gets you there:

with ordered as (
  select date, revenue, row_number() over (order by date) as rn
  from daily_metrics
  where date >= current_date - interval '90 days'
),
ema as (
  select date, revenue, revenue::numeric as ema_value, rn
  from ordered
  where rn = 1
 
  union all
 
  select o.date, o.revenue,
    round(0.3 * o.revenue + 0.7 * e.ema_value, 2) as ema_value,
    o.rn
  from ordered o
  join ema e on o.rn = e.rn + 1
)
select date, revenue, ema_value as revenue_ema
from ema
order by date;

The 0.3 is the smoothing factor (alpha). Higher alpha = more weight on recent values, faster response to changes. 0.3 is aggressive; 0.1 is sluggish. For revenue monitoring, 0.15 to 0.2 is a reasonable starting point. Fair warning: recursive CTEs aren't fast on large datasets. For 90 days of daily data it's instant. For tick-level data with millions of rows, pre-aggregate first or compute this outside SQL.

Rolling distinct counts

Window functions don't support COUNT(DISTINCT). Try it and your database will tell you so. This matters when you need something like "distinct customers in the trailing 90 days" — a sliding-window unique count over raw events.

The workaround is a lateral join that runs a subquery per row:

with daily_dates as (
  select generate_series(
    current_date - interval '180 days',
    current_date,
    interval '1 day'
  )::date as date
)
select
  d.date,
  sub.distinct_users_90d
from daily_dates d
cross join lateral (
  select count(distinct e.user_id) as distinct_users_90d
  from events e
  where e.created_at >= d.date - interval '89 days'
    and e.created_at < d.date + interval '1 day'
) sub
order by d.date;

This runs a separate COUNT(DISTINCT) for each day in your range. On a large events table without an index on created_at, it will be slow — add one. With an index and 180 days of output, it runs in under a second on tables up to a few million rows. On BigQuery, APPROX_COUNT_DISTINCT inside a window context is an alternative — not exact, but within 1-2% and much faster at scale.

Putting it together: DAU with moving averages and anomaly bands

Daily active users with 7-day and 28-day moving averages, plus rolling min/max bands for spotting anomalies:

with date_spine as (
  select generate_series(
    current_date - interval '120 days',
    current_date,
    interval '1 day'
  )::date as date
),
raw_dau as (
  select
    created_at::date as date,
    count(distinct user_id) as dau
  from events
  where event_type = 'active_session'
    and created_at >= current_date - interval '120 days'
  group by 1
),
filled as (
  select ds.date, coalesce(r.dau, 0) as dau
  from date_spine ds
  left join raw_dau r on r.date = ds.date
)
select
  date,
  dau,
  round(avg(dau) over w7, 1)  as dau_7d_ma,
  round(avg(dau) over w28, 1) as dau_28d_ma,
  min(dau) over w28            as dau_28d_min,
  max(dau) over w28            as dau_28d_max
from filled
window
  w7  as (order by date rows between 6 preceding and current row),
  w28 as (order by date rows between 27 preceding and current row)
order by date;

The WINDOW clause at the bottom avoids repeating the frame definition four times. The min/max columns give you an anomaly band — if today's DAU falls outside the 28-day min/max range, it's either a breakout or a breakdown, and either way you want to know about it. Plot the DAU as a thin line, the 7-day MA as a thicker line, and the 28-day min/max as a shaded band. Two trend lines plus an envelope is enough to tell the story in any board deck or weekly standup.

From queries to auto-refreshing dashboards

These queries are useful in a SQL editor. They're more useful on a dashboard that refreshes itself. In Fastero, each of these becomes a dashboard widget — paste the query, pick a line chart, set it to refresh daily. Attach alerts to get notified on Slack when the 7-day MA of revenue drops below a threshold, instead of watching the chart yourself.

If you're running these against Postgres, pair rolling metrics with cohort retention analysis: moving averages show what's happening at the aggregate level, cohort tables show you why.

Try Fastero free — connect your database, turn these rolling-metric queries into live dashboard widgets with auto-refresh and alerts. No credit card required.