SQL Window Functions

Q: What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

All three number rows in the order given by the ORDER BY inside OVER, and they differ only in how they treat ties. ROW_NUMBER gives every row a distinct number, breaking ties arbitrarily, so add a tiebreaker column if the result must be repeatable. RANK gives tied rows the same number and then skips ahead, producing 1, 2, 2, 4. DENSE_RANK also shares numbers between ties but never skips, producing 1, 2, 2, 3. Use ROW_NUMBER to pick exactly one row per group, and RANK or DENSE_RANK when ties should share a place.

Q: What is the difference between ROWS and RANGE in a window frame?

ROWS counts physical rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is always the current row and the two rows before it. RANGE works on the values of the ORDER BY column, so every row that ties with the current row (its peers) is included together, and an offset such as INTERVAL 7 days means a span of values rather than a number of rows. The difference matters most because the default frame with an ORDER BY is RANGE, which gives tied rows the same running total. Spell out ROWS when you want a strict row-by-row running sum.

Q: Why can't I use a window function in a WHERE clause?

Because of evaluation order. The database applies WHERE, then GROUP BY and HAVING, and only then computes window functions over the rows that survived, so at WHERE time the window values do not exist yet. Window functions are allowed only in the SELECT list and the final ORDER BY. To filter on one, compute it in a subquery or CTE and filter in the outer query, for example keeping rows where a ROW_NUMBER alias equals 1. Snowflake, BigQuery and DuckDB also offer QUALIFY, a clause that filters on window results directly.

Q: What is the difference between GROUP BY and a window function?

GROUP BY collapses each group into a single output row, so you get one total per customer and lose the individual orders. A window function computes the same kind of value but attaches it to every input row, so each order can carry its customer’s total, rank or running sum next to its own columns. Reach for GROUP BY when you want a summary and for a window when you need detail and context together, such as percent of total or change since the previous row. They also combine: a window can run over the result of a GROUP BY.

The analytics superpower most people skip: compute running totals, rankings, and row-to-row comparisons without collapsing your rows.

A window function computes a value for each row from a set of related rows — its window — without collapsing them the way GROUP BY does. PARTITION BY splits the rows into independent groups, the ORDER BY inside OVER orders each group, and the frame decides which rows around the current one an aggregate sees.

Windows are computed after WHERE, GROUP BY and HAVING, so they can use aggregates but cannot be filtered in the same query level. Examples use PostgreSQL syntax; most of it runs unchanged on MySQL 8, SQLite, SQL Server and the cloud warehouses.

The idea

What they do: Compute across a set of rows related to the current row — without GROUP BY collapsing them.
Syntax: func() OVER (PARTITION BY … ORDER BY …)
PARTITION BY: Restart the calculation per group (like GROUP BY, but rows stay).
ORDER BY (in OVER): Defines row order for running/ranking functions.
OVER (): empty window: every row of the result — a grand total beside each row
SUM(x) OVER (PARTITION BY g): any aggregate works as a window function — here the group’s total on every row
WINDOW w AS (…) … OVER w: name a window definition once and reuse it across several columns
Evaluation order: FROM → WHERE → GROUP BY → HAVING → windows → DISTINCT → ORDER BY → LIMIT

Ranking

ROW_NUMBER(): 1,2,3,4 — unique, no ties
RANK(): 1,2,2,4 — ties share, gaps after
DENSE_RANK(): 1,2,2,3 — ties share, no gaps
NTILE(4): Split rows into 4 buckets (quartiles)
Top-N per group: Wrap in a subquery: WHERE rn <= 3 after ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC)
PERCENT_RANK(): (rank − 1) / (rows − 1), between 0 and 1 — the first row is always 0
CUME_DIST(): fraction of rows at or before this one, ties included — from 1/n up to 1
Latest row per key: ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC), keep rn = 1
Ties with ROW_NUMBER: tied rows get an arbitrary order that can change between runs — add a tiebreaker column

Offsets & aggregates

LAG / LEAD: LAG(sales) OVER (ORDER BY day) — previous/next row’s value (row-to-row diffs)
Running total: SUM(amt) OVER (ORDER BY day)
Moving average: AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
First / last in window: FIRST_VALUE(x) · LAST_VALUE(x)
Share of total: 100.0 * x / SUM(x) OVER () — percent of grand total; the 100.0 avoids integer division
LAG(x, 1, 0): offset plus a default for the first row instead of NULL
NTH_VALUE(x, 2): the second value in the frame — needs a full frame, or early rows get NULL
COUNT(*) OVER (PARTITION BY g): group size on every row — filter on it without losing the detail rows
x - AVG(x) OVER (PARTITION BY g): how far each row sits from its group’s average
SUM(SUM(x)) OVER (): a window over a GROUP BY result: each group’s share of the total

Frames

ROWS vs RANGE: ROWS = physical rows; RANGE = rows with the same ORDER BY value (peers). They differ when there are ties.
Default frame trap: With ORDER BY, the default frame stops at the current row and its ties — so LAST_VALUE never looks further.
Running total window: For a true cumulative sum, add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
ROWS UNBOUNDED PRECEDING: shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING: a centered 7-row window — smoothing that does not lag behind the data
UNBOUNDED FOLLOWING: end the frame at the partition’s last row — what LAST_VALUE usually needs
RANGE INTERVAL '6 days' PRECEDING: by value, not row count: a true 7-day window even with missing days (Postgres 11+)
GROUPS 1 PRECEDING: counts tie groups instead of rows: this group of peers plus the one before (Postgres 11+)
EXCLUDE CURRENT ROW: leave the row itself out — compare it with the average of the others
No ORDER BY in OVER: the frame is the whole partition, so SUM returns the partition total on every row

Gotchas & portability

WHERE can’t see windows: Window functions run after WHERE/GROUP BY — filter on their result in an outer query or a CTE.
QUALIFY (some engines): Snowflake/BigQuery/DuckDB let you QUALIFY row_number() OVER (…) = 1 instead of a subquery.
PERCENTILE_CONT … OVER: rejected by Postgres (ordered-set aggregate): compute the median per group, then join it back
COUNT(DISTINCT x) OVER: not implemented in Postgres — pre-aggregate in a subquery, or take the max of a DENSE_RANK() computed one level down
IGNORE NULLS: LAG(x) IGNORE NULLS works in Oracle and Snowflake, not in Postgres or MySQL
Windows in UPDATE: not allowed in SET — compute them in a CTE or subquery, then join on the key
Version floor: Postgres 8.4, SQLite 3.25, MySQL 8.0, SQL Server 2012 (frames, LAG/LEAD)
Sort cost: each distinct PARTITION BY + ORDER BY means a sort; an index on (g, d) can supply it

Classic patterns

Gaps and islands: day - ROW_NUMBER() is constant within a run of consecutive days — GROUP BY it
Sessionize events: flag a gap over 30 min with LAG; a running SUM of the flags is the session id
Running total per group: SUM(x) OVER (PARTITION BY g ORDER BY d ROWS UNBOUNDED PRECEDING)
Percent of group: 100.0 * x / SUM(x) OVER (PARTITION BY g)
First event per user: MIN(ts) OVER (PARTITION BY user_id) — the cohort date on every row
Change vs previous period: x - LAG(x) OVER (ORDER BY month); LAG(x, 12) on monthly rows is year over year

Recipes

Keep only the latest row per key (dedupe)

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day DESC, id DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

The id DESC tiebreaker makes the pick deterministic when two rows share a date. On Snowflake, BigQuery or DuckDB the wrapper collapses to QUALIFY ROW_NUMBER() OVER (…) = 1.

Month-over-month change and a 3-month moving average

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change,
       AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3m
FROM monthly_sales
ORDER BY month;

The first month has no previous row, so change is NULL. The moving average uses ROWS, so a missing month silently stretches the window — use RANGE INTERVAL '2 months' PRECEDING when months can be absent.

Find streaks of consecutive days (gaps and islands)

WITH numbered AS (
  SELECT user_id, day,
         day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS island
  FROM logins
)
SELECT user_id, MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS days
FROM numbered
GROUP BY user_id, island
ORDER BY user_id, start_day;

Inside a streak the date and the row number both rise by one, so their difference stays fixed and labels the island. Deduplicate days first — two logins on one date break the arithmetic.

Split events into sessions after 30 minutes of silence

WITH flagged AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) <= INTERVAL '30 minutes'
              THEN 0 ELSE 1 END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING) AS session_id
FROM flagged;

A user’s first event has no previous row, so the comparison is NULL and falls to ELSE 1 — that is what starts session 1. Windows cannot nest, hence the CTE.

Each group’s share of the total

SELECT user_id,
       SUM(amount) AS total,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_all
FROM orders
GROUP BY user_id
ORDER BY total DESC;

The inner SUM is the GROUP BY aggregate; the outer one is a window over the grouped rows. Without 100.0, integer columns would divide to 0.

Questions about SQL Window Functions Cheat Sheet

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

All three number rows in the order given by the ORDER BY inside OVER, and they differ only in how they treat ties. ROW_NUMBER gives every row a distinct number, breaking ties arbitrarily, so add a tiebreaker column if the result must be repeatable. RANK gives tied rows the same number and then skips ahead, producing 1, 2, 2, 4. DENSE_RANK also shares numbers between ties but never skips, producing 1, 2, 2, 3. Use ROW_NUMBER to pick exactly one row per group, and RANK or DENSE_RANK when ties should share a place.

What is the difference between ROWS and RANGE in a window frame?

ROWS counts physical rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is always the current row and the two rows before it. RANGE works on the values of the ORDER BY column, so every row that ties with the current row (its peers) is included together, and an offset such as INTERVAL 7 days means a span of values rather than a number of rows. The difference matters most because the default frame with an ORDER BY is RANGE, which gives tied rows the same running total. Spell out ROWS when you want a strict row-by-row running sum.

Why can't I use a window function in a WHERE clause?

Because of evaluation order. The database applies WHERE, then GROUP BY and HAVING, and only then computes window functions over the rows that survived, so at WHERE time the window values do not exist yet. Window functions are allowed only in the SELECT list and the final ORDER BY. To filter on one, compute it in a subquery or CTE and filter in the outer query, for example keeping rows where a ROW_NUMBER alias equals 1. Snowflake, BigQuery and DuckDB also offer QUALIFY, a clause that filters on window results directly.

What is the difference between GROUP BY and a window function?

GROUP BY collapses each group into a single output row, so you get one total per customer and lose the individual orders. A window function computes the same kind of value but attaches it to every input row, so each order can carry its customer’s total, rank or running sum next to its own columns. Reach for GROUP BY when you want a summary and for a window when you need detail and context together, such as percent of total or change since the previous row. They also combine: a window can run over the result of a GROUP BY.

Go deeper

SQL cheatsheet →psql cheatsheet →