Moving Averages and NTILE
Smoothing and splitting.
Monthly orders jump up and down. A good month, a quiet month, a big sale. That makes trends hard to see. A moving average smooths the bumps, by averaging each month with the months just before it.

- Monthly numbers are bumpy
- Trends are hard to see
- Smooth them out
A 3-month moving average
Inside over, we add rows between two preceding, and current row. That's the window frame. It means, average this month and the two before it. Run it. August jumped to one hundred and twenty orders, but the moving average rose more gently, to ninety five. It shows the real trend, without the noise.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
)
SELECT month,
orders,
ROUND(AVG(orders) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3_months
FROM monthly
WHERE month >= '2025-07';โโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโโโโ โ month โ orders โ avg_3_months โ โโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโโโโค โ 2025-07 โ 77 โ 77.0 โ โ 2025-08 โ 120 โ 98.5 โ โ 2025-09 โ 93 โ 96.7 โ โ 2025-10 โ 93 โ 102.0 โ โ 2025-11 โ 83 โ 89.7 โ โ 2025-12 โ 104 โ 93.3 โ โโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโโโโ
NTILE splits into buckets
N tile splits rows into equal sized buckets. N tile four makes four quarters. Run it. The two highest earners are in quarter one, and the two lowest in quarter four.
SELECT name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quarter
FROM staff;โโโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโ โ name โ salary โ quarter โ โโโโโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโค โ Helen Ward โ 72000 โ 1 โ โ Priya Nair โ 56000 โ 1 โ โ Marcus Lee โ 54000 โ 2 โ โ Amy Chen โ 33000 โ 2 โ โ Sara Odum โ 32000 โ 3 โ โ Tom Baker โ 31000 โ 3 โ โ Noah Price โ 30500 โ 4 โ โ Leo Grant โ 29000 โ 4 โ โโโโโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโ
Summary
Rows between sets a moving window, and n tile splits rows into equal buckets. Let's put them to work.
Key takeaways- ROWS BETWEEN 2 PRECEDING
- AND CURRENT ROW
- NTILE(4) = quarters