Schema and sample data
| sale_date | amount |
|---|---|
| 2026-06-01 | 120.00 |
| 2026-06-02 | 180.00 |
| 2026-06-03 | 240.00 |
| 2026-06-04 | 300.00 |
| 2026-06-05 | 360.00 |
| 2026-06-06 | 420.00 |
| 2026-06-07 | 240.00 |
Show setup SQL (copy to run)
CREATE TABLE DailySales (
sale_date DATE PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO DailySales (sale_date, amount) VALUES
('2026-06-01', 120.00),
('2026-06-02', 180.00),
('2026-06-03', 240.00),
('2026-06-04', 300.00),
('2026-06-05', 360.00),
('2026-06-06', 420.00),
('2026-06-07', 240.00);From the DailySales table, return each sale_date and its amount, plus a moving_avg column holding the three day moving average: the average of amount over the current row and the two rows immediately before it. Compute it with a window function that uses an explicit ROWS BETWEEN 2 PRECEDING AND CURRENT ROW frame, and round the result to two decimal places. Sort the result by sale_date ascending.
Expected result
| sale_date | amount | moving_avg |
|---|---|---|
| 2026-06-01 | 120.00 | 120.00 |
| 2026-06-02 | 180.00 | 150.00 |
| 2026-06-03 | 240.00 | 180.00 |
| 2026-06-04 | 300.00 | 240.00 |
| 2026-06-05 | 360.00 | 300.00 |
| 2026-06-06 | 420.00 | 360.00 |
| 2026-06-07 | 240.00 | 340.00 |
Show hint
Reach for AVG(amount) OVER (...). Inside the OVER clause, order the rows by sale_date, then add a frame that reaches back exactly two rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. There is no GROUP BY here; every input row still produces one output row. Wrap the average in CAST(... AS DECIMAL(10,2)) to keep two decimal places.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT
sale_date,
amount,
CAST(
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS DECIMAL(10,2)
) AS moving_avg
FROM DailySales
ORDER BY sale_date;A moving average smooths a series by averaging each point with a handful of its neighbours. Here the window is three days wide: the current row plus the two before it. The AVG aggregate turns into a window function as soon as you add an OVER clause, so instead of collapsing the whole table into one average it emits one value per row. The ORDER BY sale_date inside OVER defines which rows count as "before" the current one, and the frame clause fixes exactly how many of them are pulled in.
The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "at most three physical rows: this one and the two directly above it in the ordering." Walking the sorted dates: 2026-06-03 averages 120, 180 and 240 to give 540 / 3 = 180.00; 2026-06-04 averages 180, 240 and 300 to give 720 / 3 = 240.00; and 2026-06-07 averages 360, 420 and 240 to give 1020 / 3 = 340.00. Those match the moving_avg column exactly.
Watch the edges. The first row has no preceding rows, so its frame holds a single value and the average is just its own amount, 120.00. The second row has only one row before it, so it averages two values, (120 + 180) / 2 = 150.00, not three. This is expected behaviour, not a bug: 2 PRECEDING asks for up to two earlier rows and takes however many exist. If you needed a strict three day average and wanted to hide the partial windows, you could suppress the first two rows, for example with COUNT(*) OVER (...) = 3, but the standard moving average keeps them.
Spelling out ROWS is essential. If you write ORDER BY sale_date with no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a growing cumulative average over the whole history rather than a fixed three day window. RANGE also counts by ordering-key value instead of by physical row, so it cannot express "the previous two rows" at all. The most common mistake is leaving the frame off and wondering why the numbers keep climbing. For the deeper contrast, see ROWS versus RANGE window frames. The CAST(... AS DECIMAL(10,2)) wrapper is only for presentation, because AVG over a decimal widens the scale to six places; the underlying averages are unchanged.