Smooth a noisy daily count into a 7-day rolling average

Daily numbers jump so much that a line chart of them looks like an alarm going off.

IntermediateRead 5 minDoing: under an hour7 steps

▶ Start

What you'll have at the end

A second column beside the daily count showing its 7-day rolling average, checked against one week by hand

Before you start

You need

A table with one row per day for whatever you're counting, and a query that already selects it ordered by date.

Not covered

Weighting recent days more heavily with an exponential moving average, or accounting for a real seasonal pattern; this only builds the plain, equally-weighted trailing average.

Leans on

7 steps

Where to go next

Track month over month growth with LAG

go there when the manager wants one number for up or down against last month, not a smoothed daily line

Tell seasonality apart from a real trend

go there once the smoothed line reveals a real shift, to check whether it is seasonal or a genuine trend break

Checked 26 Aug 2026

Part of the SQL for Data Analysis cookbook