Lesson 9 of 14 · 28 min
Moving averages: smoothing noisy series
By the end of this lesson you can compute trailing and centred moving averages with ROWS frames, include the history a window needs before filtering, detect partial windows, and calculate a moving ratio as a ratio of moving sums.
A moving average (or rolling average) replaces each value with the average of a fixed number of values around it. It's an AVG with a sliding frame:
- Trailing window: the current row and the N−1 before it,
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Used for dashboards, because it only needs data you already have. - Centred window: rows on both sides,
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING. Better for analysing past patterns, because it doesn't lag behind the trend, but the latest rows can't be finished until future data arrives.
This lesson uses Nakheel Hotels, a group of seven fictional hotels. One booking covers several nights, so the first step of any nightly analysis is to turn bookings into one row per occupied room-night with generate_series:
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons