Learn

Beyond PARTITION BY and ORDER BY, window functions support frames — a moving window of rows around the current row.

SELECT date, sales,
       AVG(sales) OVER (
           ORDER BY date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7day_avg
FROM daily_sales;

The ROWS BETWEEN ... AND ... defines the frame.

Frame boundaries:

• UNBOUNDED PRECEDING — start of partition
• UNBOUNDED FOLLOWING — end of partition
• CURRENT ROW — this row
• N PRECEDING / N FOLLOWING — N rows away

Frame mode:

• ROWS — physical rows
• RANGE — value-based range (peers with same ORDER BY value)

Common frames:

-- Running total (default for SUM with ORDER BY):
SUM(amount) OVER (ORDER BY date)
-- Same as: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Centered moving average (3 rows around):
AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

-- Difference from previous:
amount - LAG(amount) OVER (ORDER BY date)

-- Percentage of partition total:
amount * 100.0 / SUM(amount) OVER (PARTITION BY category)

Multiple windows in one query:

SELECT name, salary, department,
       RANK() OVER w_dept AS dept_rank,
       AVG(salary) OVER w_dept AS dept_avg,
       salary - AVG(salary) OVER w_dept AS diff_from_avg
FROM employees
WINDOW w_dept AS (PARTITION BY department ORDER BY salary DESC);

The WINDOW name AS (...) clause defines a reusable window — cleaner than repeating OVER (...) per column.

Window functions vs GROUP BY:

• GROUP BY collapses rows; window functions keep them.
• Both compute aggregates, but window functions let you see the raw data alongside the summary.

For analytics dashboards (running totals, moving averages, rankings), window frames are indispensable.