Window frames

Explore the moving frame

Change the current row and the frame. Highlighted cells are included in the sum; the outlined cell is the current row.

Here, BETWEEN specifies frame boundaries. It does not filter output rows like WHERE amount BETWEEN ....

SUM(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Interpret this as: order the rows by date, then sum the current row and up to two rows before it.

Frame recipes to remember

Worked example with visible outputs

WITH sample(day_no, revenue) AS (
    VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50)
)
SELECT day_no, revenue,
       SUM(revenue) OVER (
           ORDER BY day_no
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       SUM(revenue) OVER (
           ORDER BY day_no
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS trailing_3,
       SUM(revenue) OVER (
           ORDER BY day_no
           ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
       ) AS forward_3,
       SUM(revenue) OVER (
           ORDER BY day_no
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS centered_3
FROM sample
ORDER BY day_no;

At row 4, a three-row forward frame contains only rows 4 and 5. Frames stop at partition boundaries; SQL does not invent missing rows.

AVG averages the available non-null inputs, not a fixed denominator of three. An empty frame gives null for SUM/AVG and zero for COUNT.

Direction follows ORDER BY: with ORDER BY sale_date DESC, preceding rows have later dates. PRECEDING does not inherently mean earlier in chronological time.