Window Functions

A window function computes an aggregate or ranking value across a set of related rows — the window — while preserving every row in the result. GROUP BY collapses rows into one row per group, losing the individual row detail. Window functions never collapse rows — the result set has the same number of rows as the input, with the computed window value added as an extra column alongside the original data.

The defining syntax is the OVER() clause. Every window function requires OVER() — it is what makes the function "windowed." Inside OVER(), PARTITION BY defines which rows form the window for each row (analogous to GROUP BY), ORDER BY defines the order within the partition, and the optional frame clause defines which subset of the ordered partition to include. A bare OVER() with nothing inside uses all rows as the single window.

The power this unlocks: questions that aggregate data and need row-level detail simultaneously. "What is each order's value as a percentage of its store's total revenue?" cannot be answered with GROUP BY because after grouping, the individual orders are gone. With a window function — total_amount / SUM(total_amount) OVER (PARTITION BY store_id) — each order row retains its total_amount AND gains the store-level sum for comparison, all in the same query without subqueries or joins.

All three assign position numbers to rows within a window, but they handle ties differently. ROW_NUMBER assigns a unique sequential integer to every row — even if two rows have identical values in the ORDER BY column, they get different numbers. The ordering of ties is arbitrary (depends on physical row order) but the numbers are always unique: 1, 2, 3, 4. ROW_NUMBER is used when you need unique positions — for deduplication (keep rn = 1 per group) or pagination.

RANK assigns tied rows the same rank but then skips the next rank(s). If two rows tie for rank 2, both get rank 2 and the next row gets rank 4 (rank 3 is skipped). The sequence has gaps: 1, 2, 2, 4, 5. RANK mirrors how sports competitions work — two silver medalists means no bronze. DENSE_RANK also gives tied rows the same rank, but the next rank does not skip — 1, 2, 2, 3, 4. Dense rank is used when gaps are confusing or when you want to count distinct rank levels.

Practical choice: use ROW_NUMBER for deduplication and top-N filtering (WHERE rn = 1 gives exactly one row per group). Use RANK for competitive leaderboards where gaps signal the tied count (two rows at rank 1 followed by rank 3 shows there was no second place). Use DENSE_RANK for tier classification where you want consecutive tier numbers regardless of ties. In a query like "find the top 3 revenue stores" — with RANK, if two stores tie for first, WHERE rank <= 3 returns 4 stores (both first-place stores plus two more). With ROW_NUMBER, exactly 3 stores are returned but the tie-breaking is arbitrary.

LAG(column, offset, default) returns the value of column from the row offset positions before the current row within the window. LEAD(column, offset, default) returns the value from offset positions after the current row. The offset defaults to 1 (immediate predecessor/successor). The default parameter specifies what to return when no preceding or following row exists (the first row has no predecessor for LAG, the last row has no successor for LEAD) — if omitted, NULL is returned.

Both require ORDER BY in the OVER clause — otherwise "previous" and "next" have no defined meaning. PARTITION BY is optional: without it, the entire result set is the window; with it, the preceding/following row is within the same partition (so LAG on a customer-partitioned window gives the previous order for the same customer, not the globally previous order).

Primary use cases: period-over-period comparison — LAG(revenue) OVER (ORDER BY month) gives last month's revenue on the same row as this month's, enabling revenue - LAG(revenue) as the month-over-month change without a self-join. Customer order gap analysis — LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) gives the previous order date for each customer, enabling order_date - LAG(order_date) as the gap in days between orders. Sequential event analysis — detecting state changes (IF NEW.status != LAG(status) OVER (...) THEN ...), finding first occurrences, and computing durations between events. LAG and LEAD eliminate the verbose self-join alternative for all of these patterns.

The frame clause (ROWS BETWEEN ... AND ... or RANGE BETWEEN ... AND ...) defines which rows within the ordered partition are included in the window function calculation for the current row. Without a frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present — which is usually the intended behaviour for cumulative calculations but not for rolling windows.

ROWS uses physical row offsets — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes exactly the 7 rows: the current row and the 6 immediately preceding rows, regardless of their column values. RANGE uses value-based offsets — RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW includes all rows whose ORDER BY column value is within 6 days of the current row's value. RANGE is useful for time-series where "the last 7 days" should include all rows with dates in that range, not exactly 7 rows.

The practical difference becomes important with duplicate ORDER BY values. If 5 orders have the same order_date, ROWS BETWEEN 0 PRECEDING AND CURRENT ROW includes only the current row. RANGE BETWEEN CURRENT ROW AND CURRENT ROW includes all 5 rows with the same date (because they are all within 0 value-distance of the current row). This causes LAST_VALUE to behave unexpectedly with the default frame — LAST_VALUE with the default ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW returns the current row's value (because the current row is the last row in the frame), not the last row of the partition. The fix: explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to include all rows in the partition.

Use ROW_NUMBER (or RANK) inside a subquery or CTE to assign a rank to each order within its store partition, then filter in the outer query for ranks 1 through 3. The window function cannot be referenced in WHERE of the same query — window functions are evaluated at the SELECT stage, after WHERE. The wrapping subquery makes the rank value a regular column that the outer WHERE can filter.

Pattern: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_amount DESC) AS rn FROM orders WHERE order_status = 'Delivered') SELECT store_id, order_id, order_date, total_amount, rn FROM ranked WHERE rn <= 3 ORDER BY store_id, rn.

Choosing between ROW_NUMBER and RANK for this pattern: ROW_NUMBER always returns exactly 3 rows per store — if two orders tie for the third position, one is arbitrarily included and one is excluded. RANK returns 3 or more rows — if two orders tie for third, both get rank 3 and both are included (WHERE rank <= 3 returns 4 rows for that store). Use ROW_NUMBER when you need exactly N rows per group. Use RANK when ties should be included (a "top 3 by revenue" report where two stores are equally third should show both). DENSE_RANK is rarely needed for this pattern but useful when the rank number itself is meaningful to display (a ranking table where ranks are shown as 1, 2, 3 even with ties).

Cause: A window function was used as the argument to another window function — for example, RANK() OVER (ORDER BY SUM(col) OVER (...)) is not valid. Window functions cannot be nested directly. They can reference aggregate functions (SUM, AVG) but not other window functions in their arguments.

Fix: Break the computation into two steps using a CTE or subquery. Compute the inner window function first, then reference its result in the outer query for the second computation: WITH step1 AS (SELECT *, SUM(col) OVER (PARTITION BY grp) AS group_sum FROM table) SELECT *, RANK() OVER (ORDER BY group_sum DESC) AS rnk FROM step1. Each window function gets its own query level.

Cause: A window function result was referenced in the WHERE clause of the same query that defines it. SQL's logical execution order evaluates WHERE before SELECT — window functions are computed in the SELECT stage. The column produced by the window function does not exist yet when WHERE runs.

Fix: Wrap the window function in a subquery or CTE, then filter in the outer WHERE. Standard pattern for top-N: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM table) SELECT * FROM ranked WHERE rn <= 3. The outer WHERE sees rn as a regular column of the CTE — it exists because it was computed in the inner query.

Cause: The default window frame when ORDER BY is present is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For LAST_VALUE, the last row in this frame IS the current row — so LAST_VALUE always returns the current row's own value, not the final row of the partition. This is the most common window function bug in production.

Fix: Explicitly specify the full partition frame: LAST_VALUE(col) OVER (PARTITION BY grp ORDER BY ord ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). This makes the frame span the entire partition — the last row in the frame is genuinely the last row of the partition. Alternatively, use FIRST_VALUE with ORDER BY in reverse: FIRST_VALUE(col) OVER (PARTITION BY grp ORDER BY ord DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — this reorders the partition so the last value is now the first.

Cause: The ORDER BY inside the OVER clause has ties — multiple rows with the same ORDER BY value. When RANGE is the frame type (the default when ORDER BY is specified), all rows with the same ORDER BY value are included together, causing the cumulative sum to jump by the combined total of all tied rows at once. This can appear as a non-monotonic running total.

Fix: Use ROWS instead of the default RANGE frame: SUM(col) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). ROWS uses physical row position — strictly one row at a time. Also add a tiebreaker to the ORDER BY to eliminate ties: ORDER BY order_date, order_id — with a unique secondary sort, no two rows have the same combined ORDER BY value and the frame behaves predictably.

Cause: The OVER clause is missing ORDER BY or the ORDER BY column has no meaningful ordering. LAG requires ORDER BY to know which row is the 'previous' row. Without ORDER BY, the concept of previous is undefined and the behaviour is implementation-specific (often NULL). Alternatively, PARTITION BY is grouping rows into single-row partitions — when each partition has only one row, there is no previous row and LAG returns NULL for every row.

Fix: Add ORDER BY to the OVER clause: LAG(col) OVER (PARTITION BY grp ORDER BY sort_col). Verify that sort_col has enough distinct values to create meaningful ordering — if all rows in a partition have the same sort_col value, LAG still returns NULL for all rows because they are considered tied and there is no well-defined previous row. Add a tiebreaker: ORDER BY sort_col, id to ensure each row has a unique position.

Write a comprehensive store analytics query using at least 5 different window functions. The query should operate on delivered orders and produce one row per store per day with: store_id, city, order_date, daily_revenue, daily_orders, a 7-day moving average of daily_revenue (ma_7day), the store's rank by daily_revenue among all stores on that day (daily_rank), the cumulative revenue for the store from the start of the data (cumulative_revenue), the day-over-day change in revenue for that store (dod_change — compare to the previous day this store had an order), the store's daily_revenue as a percentage of all stores' combined revenue on that day (pct_of_day_total), and a performance label: 'Best' if daily_rank = 1, 'Top 3' if <= 3, 'Average' if daily_revenue >= overall daily average, 'Below' otherwise. Use a CTE for the daily aggregation.