SQL Window Functions — Complete Guide

Overview

Window functions compute a value across a set of rows related to the current row, without collapsing the result set into groups the way GROUP BY does. That single property solves a class of problems that are painful to write otherwise: ranking rows inside categories, comparing a row to its neighbors, or showing a running total next to every order line while still returning every order.

A concrete scenario: you want the three best-selling products per category, each with its revenue, its share of the category total, and the gap to the next product. With GROUP BY alone you’d need two aggregation passes plus a self-join back to the detail rows. With window functions it’s one readable query. The worked example later in this guide does exactly that.

Before window functions existed, these reports meant correlated subqueries, self-joins on “the previous row”, or, in old MySQL, session variables walking an ordered result. Those approaches are harder to read, harder to index for, and easy to get subtly wrong on ties. Window functions replaced them with a single declarative construct the planner can optimize.

Every major engine ships them (PostgreSQL, MySQL 8+, MariaDB 10.2+, SQL Server, Oracle, and SQLite 3.25+), so if you write analytical SQL such as reports, dashboards, or deduplication jobs, you’ll reach for them weekly.

When to Use

• You need rankings within groups (top-N per category, “latest record per customer”).
• Running totals, moving averages, or cumulative percentages are required.
• You want to compare each row to the previous or next row (month-over-month growth, session gaps).
• Aggregates must appear alongside individual row detail; GROUP BY would hide the detail.
• A self-join for row-to-row comparison would be complex or slow.

When not to reach for them: if you only need one aggregate per group with no row detail, plain GROUP BY is simpler. And if the “window” is just a handful of rows computed once per request, doing it in application code may be easier to test.

If the underlying problem is query speed rather than analytics, start with the Complete Guide to SQL Query Optimization. Window functions add at least one sort, and that doesn’t come free.

Syntax

function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

Three optional pieces control the window. PARTITION BY splits rows into independent groups. ORDER BY sorts rows inside each partition. The frame clause (ROWS, RANGE, or GROUPS) picks which rows around the current one feed the function.

Two defaults matter. Omit PARTITION BY and the whole result set becomes one partition. Omit the frame and you get RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, a subtle default that’s the top source of wrong results. The frame section below covers it.

When several functions share the same window, declare it once:

SELECT
    employee_id,
    RANK() OVER w,
    PERCENT_RANK() OVER w
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Named WINDOW clauses work in PostgreSQL, MySQL 8, MariaDB, and SQLite, but not in SQL Server.

How Window Functions Execute

Logically, window functions run late in query evaluation: after FROM, WHERE, GROUP BY, and HAVING, roughly alongside SELECT, and before DISTINCT and the final ORDER BY. Two practical consequences follow:

1. You can’t put a window function in WHERE or GROUP BY, because the window doesn’t exist yet at that stage. Filter window results in an outer query or a CTE instead.
2. Window functions see the grouped rows, not raw table rows. SUM(amount) OVER () after GROUP BY month sums the monthly totals, not the raw line items.

Every input row produces exactly one output row; nothing collapses. That’s the core difference from GROUP BY, and it’s why window functions compose so well inside CTEs. If you end up stacking aggregates and windows in one query, the SQL CTE guide covers the pattern for keeping it readable.

One thing worth knowing for performance: each distinct OVER() specification (each unique PARTITION BY/ORDER BY combination) needs its own sorted pass over the data. A SELECT with three different window definitions typically sorts three times. Sharing a named WINDOW or aligning the partitioning lets the engine reuse a single sort.

Window Functions vs GROUP BY

	GROUP BY	Window function
Output rows	One per group	Same as input rows
Row detail preserved	No	Yes
Usable in WHERE	No (aggregates via HAVING)	No — filter in an outer query
Typical use	”Total per category"	"Each row next to its category total”

Rule of thumb: if your SELECT mixes detail columns and aggregates and you didn’t write GROUP BY, you probably want a window function.

Ranking Functions

Function	Behavior	Duplicate Handling
ROW_NUMBER()	Sequential integer per partition	No ties; arbitrary order for duplicates
RANK()	Rank with gaps	Same value gets same rank; next rank skips
DENSE_RANK()	Rank without gaps	Same value gets same rank; next rank continues

-- Top 3 products by revenue in each category
WITH ranked AS (
    SELECT
        product_id,
        category,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
    FROM product_revenue
)
SELECT * FROM ranked WHERE rank <= 3;

Two details bite people in production. First, ROW_NUMBER is non-deterministic on ties: two rows with the same revenue can swap positions between runs. Add a tiebreaker (ORDER BY revenue DESC, product_id) if the result feeds deduplication or a report someone will diff. Second, RANK produces gaps: with two products tied at rank 1, the next product is rank 3, so WHERE rank <= 3 can return four rows. Choose ROW_NUMBER for “exactly N rows per partition”, DENSE_RANK for “all rows in the top N ranks”, and RANK when tied positions should consume rank numbers.

Offset Functions: LAG and LEAD

LAG(expr, n) returns expr from the row n positions before the current row; LEAD looks forward. Both accept an optional default for the edge rows: LAG(revenue, 1, 0) returns 0 instead of NULL on the first row.

-- Compare current month to previous month
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month_change,
    LEAD(revenue) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

When you turn the difference into a percentage, guard the division with NULLIF, since a zero or NULL previous month otherwise returns NULL or raises a division error. The worked example below shows the full pattern.

One gotcha with time series: LAG returns the previous row, not the previous period. If a month has no orders, the row is missing entirely and LAG reaches back to the last month that exists. When gaps in the calendar matter, join your data to a date-spine table (or generate_series in PostgreSQL) so every period has a row before applying LAG.

Value Functions

FIRST_VALUE, LAST_VALUE, and NTH_VALUE return a column from a specific position inside the frame:

-- Compare each salary to the top salary in the department
SELECT
    employee_id,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS top_salary,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS gap_to_top
FROM employees;

LAST_VALUE hides a famous trap: with the default frame (RANGE ... AND CURRENT ROW) it never sees rows after the current one, so it returns the current row’s own value. To get the real last value, extend the frame explicitly:

LAST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Aggregate Window Functions

Any aggregate (SUM, AVG, COUNT, MIN, MAX) works as a window function:

-- Running total and 7-day moving average
SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM orders;

SUM(amount) OVER (ORDER BY order_date) relies on the default frame, which is fine for running totals on unique dates, but read the next section before trusting it on data with duplicate sort keys.

In PostgreSQL and SQLite, aggregate window functions also accept FILTER (WHERE ...), which restricts which frame rows feed the aggregate. For example, SUM(amount) FILTER (WHERE status = 'paid') OVER (ORDER BY order_date) gives a running total of paid orders only. MySQL and SQL Server don’t support FILTER; the portable equivalent is a CASE inside the aggregate: SUM(CASE WHEN status = 'paid' THEN amount END) OVER (...).

Frame Clauses: ROWS, RANGE, and GROUPS

Frame	Meaning
ROWS BETWEEN n PRECEDING AND CURRENT ROW	Physical rows: n rows back through current
ROWS UNBOUNDED PRECEDING	From partition start to current row
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW	Logical window on the ORDER BY value
GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING	Peer groups: tied rows count as one

ROWS counts physical rows. RANGE counts values: it includes every row whose ORDER BY value falls inside the range, so ties are always peers. GROUPS counts distinct sort positions, letting tied rows move together while keeping positional offsets. GROUPS requires PostgreSQL 11+ or SQLite; RANGE with INTERVAL works in PostgreSQL, MySQL 8, and Oracle but not SQL Server.

The critical detail: when you write ORDER BY inside OVER() with no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. On duplicate sort keys, tied rows enter the frame together, and a running total jumps in steps instead of accumulating row by row:

-- Two orders on the same date -> RANGE includes BOTH for each row
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS stepped_total,
    SUM(amount) OVER (
        ORDER BY order_date ROWS UNBOUNDED PRECEDING
    ) AS true_running_total
FROM orders;

If you want row-by-row accumulation, write ROWS. Use RANGE when the value defines the window (“all rows within 7 days”). Reach for GROUPS when ties should move together but you still want positional offsets.

Dialect Support

Engine	Since	Notes
PostgreSQL	8.4 basic, 11 for GROUPS	Most complete implementation
MySQL	8.0	No GROUPS; RANGE with INTERVAL supported
MariaDB	10.2	Similar to MySQL 8
SQL Server	2012 partial, 2022 for IGNORE NULLS	No named WINDOW; no GROUPS; RANGE limited to UNBOUNDED/CURRENT ROW
Oracle	8i	Full frame support, including RANGE INTERVAL
SQLite	3.25	Full support, including GROUPS

Two portability rules: test RANGE frames with INTERVAL on your target engine before relying on them, and skip named WINDOW clauses if the query must also run on SQL Server.

Real-World Examples

Deduplication (keep latest per group)

WITH ranked AS (
    SELECT
        customer_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
    FROM customer_profiles
)
SELECT customer_id, email, updated_at FROM ranked WHERE rn = 1;

Deduplication is where ROW_NUMBER earns its keep. The same pattern removes duplicate events, picks the newest price, or keeps the latest address per customer. For more ranking variants such as dense ties or paginated top-N, see the window functions ranking recipe.

Percentile and quartile buckets

SELECT
    employee_id,
    department,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary) AS quartile,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS percentile
FROM employees;

NTILE(4) splits each department into four roughly equal buckets, while PERCENT_RANK returns a 0–1 position that’s handy for “top 10%” filters: wrap the query and apply WHERE percentile >= 0.9 outside. CUME_DIST is the complementary function: it returns the fraction of rows at or below the current value.

Sessionization with gaps and islands

A classic analytics problem: group a user’s events into sessions where a gap longer than N minutes starts a new session. The trick is comparing each event to the previous one with LAG, flagging new sessions, then accumulating the flag:

WITH flagged AS (
    SELECT
        user_id,
        event_time,
        CASE
            WHEN event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            ) > INTERVAL '30 minutes'
            THEN 1 ELSE 0
        END AS new_session
    FROM events
)
SELECT
    user_id,
    event_time,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM flagged;

session_id increments every time the gap exceeds 30 minutes, with no cursors or procedural code. I’ve used this pattern to sessionize click streams and IoT event logs without touching application code. The interval syntax shown is PostgreSQL; on MySQL use TIMESTAMPDIFF(MINUTE, prev, curr), on SQL Server DATEDIFF.

Worked Example: Sales Analysis

Putting it together on a realistic table (orders(id, customer_id, order_date, product_category, amount) holding 10M rows over two years), four window patterns cover most sales reporting.

Top 3 products per category per month uses RANK over a monthly aggregate. Note the window sits on top of GROUP BY output, which is why SUM(amount) appears inside the ORDER BY of the window:

WITH monthly_category_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        product_category,
        product_id,
        SUM(amount) AS total_revenue,
        RANK() OVER (
            PARTITION BY DATE_TRUNC('month', order_date), product_category
            ORDER BY SUM(amount) DESC
        ) AS rank_in_category
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY DATE_TRUNC('month', order_date), product_category, product_id
)
SELECT month, product_category, product_id, total_revenue, rank_in_category
FROM monthly_category_sales
WHERE rank_in_category <= 3
ORDER BY month DESC, product_category, rank_in_category;

Month-over-month growth chains LAG calls. The NULLIF wrapper prevents division by zero on the first month of each category:

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        product_category,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date), product_category
)
SELECT
    month,
    product_category,
    revenue,
    LAG(revenue) OVER (PARTITION BY product_category ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (PARTITION BY product_category ORDER BY month) AS abs_change,
    ROUND((
        (revenue - LAG(revenue) OVER (PARTITION BY product_category ORDER BY month))
        / NULLIF(LAG(revenue) OVER (PARTITION BY product_category ORDER BY month), 0)
    ) * 100, 2) AS pct_change
FROM monthly_totals
ORDER BY product_category, month;

Running totals and share-of-year combine a partitioned frame with an empty OVER (), which is the idiomatic way to get a grand total next to detail rows:

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total,
    ROUND(
        revenue / NULLIF(SUM(revenue) OVER (), 0) * 100, 2
    ) AS pct_of_year,
    ROUND(
        AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
        2
    ) AS three_month_avg
FROM monthly_totals
ORDER BY month;

Finally, customer segmentation by spending quartile mixes NTILE and PERCENT_RANK over a per-customer aggregate:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS lifetime_value,
        COUNT(*) AS order_count,
        NTILE(4) OVER (ORDER BY SUM(amount) DESC) AS spending_quartile,
        PERCENT_RANK() OVER (ORDER BY SUM(amount) ASC) AS percentile
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
)
SELECT
    customer_id,
    lifetime_value,
    order_count,
    spending_quartile,
    ROUND(percentile * 100, 1) AS percentile_pct
FROM customer_totals
WHERE spending_quartile = 1  -- Top 25%
ORDER BY lifetime_value DESC;

These four patterns (top-N per group, period-over-period deltas, running totals with share-of-total, and quantile segmentation) cover most analytical reporting. They’re also composable: nothing stops you from ranking customers inside a spending_quartile or computing month-over-month change on the session counts from the earlier example. The habit worth building is reading any analytical requirement as “which rows surround this row, and what do I compute over them.” Framed that way, the PARTITION BY, ORDER BY, and frame usually write themselves.

Performance Considerations

Window functions add work after aggregation: at minimum one sort per distinct PARTITION BY/ORDER BY combination, then a WindowAgg (PostgreSQL) or Window Spool (SQL Server) node on top. On large tables:

• Create composite indexes matching PARTITION BY + ORDER BY. For PARTITION BY category ORDER BY revenue DESC, an index on (category, revenue DESC) can feed the sort directly.
• For LAG/LEAD over time series, an index on (entity_id, event_time) avoids a separate sort.
• Run EXPLAIN ANALYZE and look for an explicit Sort above WindowAgg. If it’s there and slow, the index isn’t being used. Watch for sorts spilling to disk (Disk in the plan); raising work_mem in PostgreSQL often fixes that.
• Partition the table by date when queries always filter a time range; partition pruning shrinks the window input before sorting.
• Reusing one named WINDOW for several functions lets the engine share a single sort instead of re-sorting per function.

For deeper work on execution plans and indexing strategy, see the SQL performance tuning guide.

Best Practices

• Always write ORDER BY inside OVER() for offset and aggregate windows, since the result is meaningless on unordered data.
• Add a deterministic tiebreaker (ORDER BY revenue DESC, id) when the output feeds deduplication, pagination, or a diffable report.
• Prefer ROWS frames for row-counting windows and RANGE only when the sort value defines membership; know the default frame.
• Put window results behind a CTE and filter in the outer query, since you can’t reference the window output in WHERE anyway.
• Name shared windows with WINDOW w AS (...) when a query repeats the same partition and ordering (not on SQL Server).
• Guard growth-rate divisions with NULLIF(denominator, 0).

Common Mistakes

• Forgetting PARTITION BY: the window applies to the entire result set, so every “per category” metric becomes a global one.
• Trusting the default frame: RANGE UNBOUNDED PRECEDING treats ties as peers; running totals step instead of accumulate on duplicate sort keys.
• Using ROW_NUMBER without a tiebreaker: non-deterministic output on ties means dedup keeps a different “latest” row between runs.
• Putting window functions in WHERE: they execute after WHERE; the query errors out. Filter in an outer query or CTE.
• Mixing GROUP BY and windows carelessly: windows see grouped rows, not raw rows. Use a CTE so each level is explicit.
• Expecting LAG to return a real value on the first row: it’s NULL unless you pass a default; LEAD hits the same edge case on the last row.
• No index on PARTITION BY/ORDER BY columns: the sort spills to disk on large tables.

Troubleshooting

• Running total jumps in steps: you’re on the default RANGE frame with duplicate ORDER BY values. Switch to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or add a tiebreaker.
• LAST_VALUE returns the current row’s value: the default frame ends at the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• “Window functions are not allowed in WHERE” (PostgreSQL) or equivalent errors: move the filter to an outer query over a CTE.
• Query is fast on a few thousand rows but slow in production: check EXPLAIN ANALYZE for a sort spilling to disk; add the composite index and review work_mem.
• NTILE buckets look uneven: remainder rows go to the first buckets by design. Use PERCENT_RANK cutoffs when you need exact percentile boundaries.

Quick Reference

Need	Function	Pattern
Unique sequence per group	ROW_NUMBER()	dedup: WHERE rn = 1
Rank with gaps on ties	RANK()	leaderboards
Rank without gaps on ties	DENSE_RANK()	”top N distinct levels”
Previous / next row	LAG() / LEAD()	LAG(col, 1, 0) sets a default
Value at a frame position	FIRST_VALUE() / LAST_VALUE() / NTH_VALUE()	mind the frame on LAST_VALUE
Running total	SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)	ROWS for determinism
Moving average	AVG() OVER (... ROWS BETWEEN n PRECEDING AND CURRENT ROW)	n-row lookback
Distribution position	PERCENT_RANK() / CUME_DIST() / NTILE(k)	percentile cutoffs

Further Reading

• PostgreSQL documentation: Window Functions and the window frame reference
• MySQL 8.4 Reference: Window Functions
• SQL Server documentation: SELECT - OVER clause
• SQLite documentation: Window Functions
• Related on this site: SQL joins guide for the join patterns that pair with windowed queries.