AI Hints
Homeโ€บSQL Tutorialโ€บRunning Totals and Trends
SQL ยท Lesson 68 of 126

Moving Averages and NTILE

Smoothing and splitting.

โฑ 2 min readโ–ถ Video at 1:36:16๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Moving Averages and NTILE
From the video: Moving Averages and NTILE
  • 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.

query.sqlsql
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';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
SELECT name,
       salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quarter
FROM staff;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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
Next up: Practice: Who Brings the Money? โ€” NTILE on real spending