SQL Window Functions – ROW_NUMBER, RANK, LAG, SUM OVER

What Are Window Functions?

A window function computes a value for the current row by looking at a "window" of related rows defined by the OVER clause. All rows remain in the output — nothing is collapsed.

Feature	GROUP BY	Window Functions
Output rows	One per group	Same as input
Access to individual rows?	No (collapsed)	Yes
Aggregate per partition?	Yes	Yes (via OVER)
Ranking, lead/lag?	No	Yes

The OVER Clause

Every window function requires an OVER clause. An empty OVER means "the entire result set is the window":

SQL

SELECT
    name,
    salary,
    AVG(salary) OVER ()   AS company_avg_salary  -- window = all rows
FROM employees;

PARTITION BY

PARTITION BY divides the rows into partitions (like GROUP BY, but rows are not collapsed). The window function resets and runs independently for each partition:

SQL

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

▶ Result (each row keeps its own salary AND sees the dept average)

ORDER BY Inside OVER

Adding ORDER BY inside OVER defines the order within each partition. This is required for ranking functions and changes the frame for aggregate functions (from full partition to cumulative up-to-current-row):

SQL

SELECT
    name,
    department,
    salary,
    -- Cumulative sum within each department, ordered by salary desc
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_salary
FROM employees;

ROW_NUMBER()

Assigns a unique sequential integer to each row within its partition, with no gaps or ties:

SQL

-- Assign row numbers within each department, ordered by salary desc
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

-- Get the top earner per department
SELECT * FROM (
    SELECT
        name, department, salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn = 1;

RANK() and DENSE_RANK()

Both assign a rank based on ORDER BY. They differ in how they handle ties:

SQL

SELECT
    name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no_gaps
FROM employees;

Function	Ties?	Next rank after tie?	Example (two employees tied at #2)
RANK()	Same rank	Skips (3 becomes 4)	1, 2, 2, 4
DENSE_RANK()	Same rank	Continues (no skip)	1, 2, 2, 3
ROW_NUMBER()	No ties (arbitrary)	N/A	1, 2, 3, 4

LAG() and LEAD()

LAG and LEAD access values from previous or next rows within the partition, enabling row-over-row comparisons without self-joins:

SQL

-- Month-over-month revenue comparison
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0)  OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS mom_change,
    LEAD(revenue, 1)    OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;
-- LAG(col, offset, default): offset=rows back, default=value if no previous row

Running Total with SUM OVER

A running (cumulative) total is one of the most common window function use cases:

SQL

SELECT
    order_date,
    department,
    amount,
    SUM(amount) OVER (
        PARTITION BY department
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY department, order_date;

💡

Common window frame options

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — cumulative total from first row to current.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — 3-row moving average.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — full partition (same as no frame clause).

Summary

📋 Summary

Window functions compute across related rows using OVER() without collapsing rows — unlike GROUP BY.
PARTITION BY divides rows into groups; the function resets per partition.
ORDER BY inside OVER defines row ordering within a partition, required for ranking and cumulative functions.
ROW_NUMBER() assigns unique sequential numbers; RANK() has gaps on ties; DENSE_RANK() has no gaps.
LAG(col, n) accesses the value n rows before; LEAD(col, n) accesses n rows ahead.
SUM OVER with an ORDER BY inside OVER computes running totals.

FAQ

Can I use window functions in a WHERE clause? +

No. Window functions are evaluated after WHERE and GROUP BY (at the SELECT step), so you cannot reference them in WHERE or HAVING. To filter on a window function result, wrap the query in a subquery or CTE: SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM ...) t WHERE t.rn = 1.

Can I combine GROUP BY with window functions? +

Yes. You can use window functions on top of a grouped result. First apply GROUP BY to aggregate the data, then apply a window function to the aggregated rows. Example: group by month to get monthly totals, then use SUM OVER to compute a cumulative total across months.

Are window functions available in all databases? +

All major databases support window functions: PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2008+, Oracle 8i+, SQLite 3.25+. If you are on MySQL 5.x or an older SQLite version, window functions are not available — consider upgrading or rewriting as correlated subqueries.

What is the difference between NTILE and PERCENT_RANK? +

NTILE(n) divides rows into n roughly equal buckets and assigns a bucket number (1 to n) — useful for quartile/decile analysis. PERCENT_RANK() computes a value between 0 and 1 representing a row's relative position within its partition. Neither function is covered in this lesson, but both use the same OVER syntax as RANK and DENSE_RANK.