SQL Window Functions: SUM, AVG, LAG, and LEAD

Posted by Kyle Hankinson January 25, 2026

---

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into groups like GROUP BY does. They are one of the most powerful features in modern SQL.

The OVER() Clause

Every window function uses OVER() to define which rows to include:

SELECT name, department, salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

This adds a total_salary column with the sum of all salaries — without grouping. Every row still appears individually.

PARTITION BY

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing):

SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Each row shows the department total and average alongside the individual salary.

ORDER BY in OVER()

Adding ORDER BY creates a running calculation:

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

order_date	amount	running_total
Jan 1	100	100
Jan 2	150	250
Jan 3	75	325
Jan 4	200	525

LAG and LEAD

Compare the current row to previous or next rows:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

month	revenue	prev_month	month_over_month
Jan	10000	NULL	NULL
Feb	12000	10000	2000
Mar	11500	12000	-500

LAG(col, n) looks back n rows (default 1). LEAD(col, n) looks forward n rows.

Default Values

Avoid NULLs for the first/last row:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- returns 0 instead of NULL

FIRST_VALUE and LAST_VALUE

SELECT name, department, salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

Percent of Total

SELECT name, department, salary,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

Moving Average

Use a frame specification to average over a sliding window:

SELECT order_date, amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM daily_sales;

Frame Types

Frame	Meaning
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW	Current row + 2 rows before
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	All rows from start to current (default for running totals)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	3-row window centered on current
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	All rows in partition

Combining Multiple Window Functions

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_company
FROM employees
ORDER BY department, salary DESC;

WINDOW Clause (Reusable Definitions)

Avoid repeating the same OVER clause:

SELECT
    order_date, amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM orders
WINDOW w AS (ORDER BY order_date);

Supported in PostgreSQL, MySQL 8.0+, and SQLite 3.28+. Not supported in SQL Server or Oracle.

Database Compatibility

Feature	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
Basic window functions	8.0+	8.4+	2005+	8i+	3.25+
LAG / LEAD	8.0+	8.4+	2012+	8i+	3.25+
Frame specification	8.0+	8.4+	2012+	8i+	3.28+
WINDOW clause	8.0+	Yes	No	No	3.28+

---