A Moving Average in Python

An interviewer asks: "For each order, show the average total of that order and the two orders before it." That is a moving average (or rolling average): each value is averaged with the few just before it, so one big order moves the line less.

In SQL it is a window function with a frame. ORDER BY inside OVER (…) lines the rows up, and ROWS BETWEEN n PRECEDING AND CURRENT ROW keeps the current row and the n rows before it. A 7-day average on a table of daily site visits looks like this:

query = (
    "SELECT day, visits, "
        "AVG(visits) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_avg "
    "FROM visits "
)

At the start the frame isn't full: the first row has no rows before it, so its average is over 1 row, the second over 2. COUNT(*) over the same frame shows how many rows each average used. And ROWS counts rows, not days: the two orders before may be weeks apart.

Your code starts with a given WITH totals AS (…): one row per order that wasn't cancelled, with its id, ordered date and total. Your query must print:

id | ordered | total | orders_in_avg | moving_avg
1 | 2026-03-02 | 34 | 1 | 34
2 | 2026-03-15 | 29 | 2 | 31.5
3 | 2026-04-05 | 41 | 3 | 34.7
4 | 2026-04-18 | 27 | 3 | 32.3
5 | 2026-05-07 | 73 | 3 | 47
7 | 2026-05-30 | 62 | 3 | 54
(6 rows)

Your task: below the given totals, select id, ordered, total, then COUNT(*) over the frame as orders_in_avg and the AVG of total over this order and the 2 before it, in date order, rounded to 1 decimal, as moving_avg, FROM totals.

Assignment
Follow the Coding Tutorial to write the SELECT and FROM lines, then the moving average between them, then the count of rows in each frame.

Hint
Both window functions use the same OVER (ORDER BY ordered ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Wrap the average in ROUND(…, 1).