Advanced
Case 40 of 50
Case 040: The Rolling Average
customersorderspayments
The running total from before keeps climbing forever, which makes it hard to tell if a customer's spending has actually shifted recently — one huge early order can bury a recent slowdown for months.
The manager wants something that reacts to recent behavior instead: "Running totals are useful, but they just keep climbing forever. I want a smoothed trend — each order's value averaged with the couple before it."
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“For each customer, show every order's date and amount, along with a rolling average of the last 3 orders (the current one and the 2 before it), in date order.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT
customers.name,
orders.order_date,
payments.amount,
ROUND(AVG(payments.amount) OVER (
PARTITION BY customers.id
ORDER BY orders.order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 1) AS rolling_avg_last_3
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN payments ON payments.order_id = orders.id
ORDER BY customers.name, orders.order_date;
What that query returns
Case Debrief
Window frames (ROWS BETWEEN)
- ROWS BETWEEN defines exactly which rows a window function looks at, relative to the current row — this case's ROWS BETWEEN 2 PRECEDING AND CURRENT ROW always covers exactly 3 rows: the current one and the 2 right before it.
- A running total leaves that frame open (growing from the start of the partition with every row); a moving average bounds it to a fixed size like this one, so old orders eventually slide back out instead of piling up forever.