SQL Window Functions — ROW_NUMBER, RANK, LAG and Practical Examples

1. Why Window Functions Beat GROUP BY

GROUP BY is a reduction: N rows go in, one row per group comes out. That is exactly what you want for a summary report — and exactly wrong when you need detail rows plus context. Say you want every order listed alongside that customer's lifetime order total. With GROUP BY you either lose the individual orders or bolt on a self-join:

-- The GROUP BY-era workaround: join the table to its own aggregate
SELECT o.order_id, o.customer_id, o.amount, t.customer_total
FROM orders o
JOIN (
  SELECT customer_id, SUM(amount) AS customer_total
  FROM orders
  GROUP BY customer_id
) t ON t.customer_id = o.customer_id;

The window function version does the same job in one pass, with no join and no duplicated scan of the table:

SELECT order_id,
       customer_id,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Each order row survives intact; the aggregate is simply attached to it. The execution order explains why this works: window functions are evaluated after WHERE, GROUP BY, and HAVING, and before ORDER BY and LIMIT. Two consequences worth memorizing:

• You cannot filter on a window function result in the same query's WHERE — WHERE has already run. Wrap the query in a CTE or subquery and filter outside (every Top-N example below does this).
• You can mix window functions with GROUP BY: the grouping collapses rows first, then the window function sees one row per group.

2. The OVER Clause: PARTITION BY, ORDER BY, Frame

Every window function has the same shape. The function itself (ROW_NUMBER, SUM, LAG...) computes a value; the OVER clause defines which rows it sees:

function_name(arguments) OVER (
    PARTITION BY column          -- split rows into independent groups
    ORDER BY column              -- impose an order inside each group
    ROWS BETWEEN ... AND ...     -- optional frame: narrow the rows further
)

The three parts do different jobs:

• PARTITION BY divides the result set into partitions; the function restarts for each one. Omit it and the entire result set is a single partition. Note the name: it is PARTITION BY, not GROUP BY — no collapsing happens.
• ORDER BY sorts rows within each partition. It is mandatory for ranking functions (ROW_NUMBER, RANK) and LAG/LEAD, optional for aggregates like SUM. It also changes the default frame — more on that trap in section 5.
• The frame (ROWS/RANGE clause) further restricts which rows inside the partition feed the calculation, e.g. "the current row and the six before it" for a 7-day moving average.

A concrete example combining all three — each employee's salary, their department's payroll, and their rank within the department:

SELECT name,
       department,
       salary,
       SUM(salary) OVER (PARTITION BY department)                    AS dept_payroll,
       RANK()      OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Both MySQL 8+ and PostgreSQL also let you define a window once and reuse it, which keeps wide SELECT lists readable:

SELECT name,
       SUM(salary)  OVER w AS dept_payroll,
       AVG(salary)  OVER w AS dept_avg,
       RANK()       OVER w AS dept_rank
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

3. ROW_NUMBER vs RANK vs DENSE_RANK (Ties Demo)

The three ranking functions behave identically until two rows tie — then they diverge, and picking the wrong one silently breaks "top 3" logic. Here is exam data engineered with ties:

-- exam_scores
-- name   score
-- Alice  95
-- Bob    95
-- Carol  92
-- Dave   90
-- Eve    90
-- Frank  90
-- Grace  88

SELECT name,
       score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_scores;

Result:

Read the divergences carefully:

• ROW_NUMBER — always unique, always sequential. Ties are broken arbitrarily: run the query twice and Alice/Bob may swap. If determinism matters, add a tiebreaker: ORDER BY score DESC, name.
• RANK — tied rows share a rank, and the next rank skips (1, 1, 3). This is Olympic-medal semantics: two golds, no silver.
• DENSE_RANK — tied rows share a rank, no skipping (1, 1, 2). The rank always equals "how many distinct higher values exist, plus one."

Which one you want depends on the question. "Give me exactly 3 rows per group" → ROW_NUMBER. "Give me everyone in the top 3 scores" → RANK (may return more than 3 rows). "Give me rows at the top 3 distinct salary levels" → DENSE_RANK. Notice RANK() <= 3 above returns six people, while ROW_NUMBER() <= 3 would cut Frank and Eve on a coin flip — that difference is the entire point of this section.

4. LAG and LEAD: Period-over-Period Comparisons

LAG reaches back to a previous row within the partition; LEAD reaches forward. Before window functions, month-over-month growth required a self-join of a table to itself offset by one month — painful to write and worse to read. Now:

SELECT month,
       revenue,
       LAG(revenue)  OVER (ORDER BY month) AS prev_revenue,
       LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM monthly_revenue;

The full signature is LAG(expr, offset, default). The offset defaults to 1; the default value substitutes for the NULL you would otherwise get at the partition edge (the first row has no predecessor, the last has no successor). Year-over-year is just LAG(revenue, 12) on monthly data.

Growth percentage — the number every dashboard wants:

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(
         (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
       1) AS growth_pct
FROM monthly_revenue;

Two production details: NULLIF(..., 0) guards against division by zero when the previous month had no revenue, and if you need to filter on growth (say, months that declined), you must wrap the query — window results are invisible to WHERE:

WITH growth AS (
  SELECT month,
         revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT month, revenue, prev_revenue
FROM growth
WHERE revenue < prev_revenue;   -- declining months only

LAG/LEAD respect PARTITION BY like everything else: add PARTITION BY region and each region's timeline is compared only against itself, never bleeding across the boundary.

5. Running Totals and the Frame Clause

Add ORDER BY to an aggregate window function and you get a running total — the most-used window pattern in analytics:

SELECT user_id,
       txn_date,
       amount,
       SUM(amount) OVER (PARTITION BY user_id
                         ORDER BY txn_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions;

Each row shows the sum of that user's transactions from the beginning of time up to and including the current row. The ROWS BETWEEN ... AND ... part is the frame clause, and it is worth understanding rather than copy-pasting, because the default will bite you.

The default-frame trap. When a window has ORDER BY but no explicit frame, SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE means "all rows that are peers of the current row under the ORDER BY" — so if two transactions share the same txn_date, both get the same running total that already includes both of them. ROWS counts physical rows instead and advances one row at a time. Rule of thumb: for running totals over data with possibly duplicated sort keys, always write ROWS explicitly.

The frame vocabulary, in full:

A frame must be a contiguous span, so legal windows look like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (a trailing 7-row window) or ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (the whole partition — which is what SUM(amount) OVER (PARTITION BY user_id) with no ORDER BY gives you). Both MySQL 8+ and PostgreSQL support the full ROWS/RANGE syntax, including EXCLUDE in PostgreSQL and GROUPS in both.

Quick check when a running total looks wrong: print COUNT(*) OVER (...same frame...) next to it. If the count jumps by 2 on some rows, you are on the default RANGE frame with tied sort values.

6. Three Practical Patterns

6.1 Top-N per group — the canonical row_number over partition by

"Three highest-paid employees per department" cannot be expressed with GROUP BY: MAX(salary) gives you the value but not the rest of the winning row, and self-joining on the max breaks when two people tie. ROW_NUMBER() OVER (PARTITION BY ...) is the standard answer:

SELECT department, name, salary
FROM (
  SELECT department,
         name,
         salary,
         ROW_NUMBER() OVER (PARTITION BY department
                            ORDER BY salary DESC, employee_id) AS rn
  FROM employees
) ranked
WHERE rn <= 3;      -- WHERE can't see rn until it's an outer query

Details that matter in production: the inner query computes the rank, the outer one filters it (window functions run after WHERE, hence the nesting). employee_id as a secondary sort key makes the result deterministic when salaries tie — without it, "who is #3" can change between runs. Swap ROW_NUMBER for DENSE_RANK if ties should all qualify, or RANK for strict podium semantics. MySQL and PostgreSQL both optimize this into a single sort per partition.

6.2 Consecutive login streaks — gaps and islands

Detecting runs of consecutive days is a classic interview question and a real churn/engagement metric. The trick: for consecutive dates, date minus its row number is constant. That constant identifies each "island" of consecutive days:

-- MySQL 8+: find every login streak of 7+ days
WITH distinct_logins AS (
  SELECT DISTINCT user_id, login_date          -- multiple logins/day would break the math
  FROM logins
),
grouped AS (
  SELECT user_id,
         login_date,
         DATE_SUB(login_date,
                  INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id
                                              ORDER BY login_date) DAY) AS island
  FROM distinct_logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS streak_days
FROM grouped
GROUP BY user_id, island
HAVING COUNT(*) >= 7
ORDER BY streak_days DESC;

Walk through it: within a streak, day 1 minus 1, day 2 minus 2, day 3 minus 3 all land on the same date — the island ID. The moment a day is skipped, the subtraction result shifts and a new island begins. The outer GROUP BY then measures each island. In PostgreSQL the only change is the date arithmetic:

-- PostgreSQL variant of the island expression
login_date - (ROW_NUMBER() OVER (PARTITION BY user_id
                                 ORDER BY login_date))::int AS island

The DISTINCT in the first CTE is not optional — a user logging in twice on Tuesday would consume two row numbers for one date and fracture the streak.

6.3 Moving averages with a sliding frame

A 7-day moving average smooths noise in daily metrics. The frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is literally "this row plus the six before it":

SELECT day,
       close_price,
       ROUND(AVG(close_price) OVER (ORDER BY day
                                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma_7day,
       COUNT(*)              OVER (ORDER BY day
                                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)     AS rows_in_frame
FROM stock_prices;

For the first six rows the frame is shorter than seven, so the "moving average" is really a partial average — the rows_in_frame column makes that visible. Two ways to handle it depending on taste: filter with an outer query (WHERE rows_in_frame = 7), or keep the partial values and let the charting layer decide. If your data has missing days (weekends, market holidays), ROWS counts records, not calendar days — switch to RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW in PostgreSQL, or RANGE BETWEEN 6 PRECEDING over a numeric day column, to average over true calendar windows.

7. Version Support and Performance Notes

Window functions are standard SQL:2003, but "standard" and "your production server" are different things:

On performance, the mental model is simple: each distinct window definition (unique combination of PARTITION BY, ORDER BY, and frame) costs one sort or hash pass. Functions sharing a window definition share that pass — PostgreSQL will explicitly show a single WindowAgg node stacked over one Sort. Practical consequences:

• An index on (partition_column, order_column) can let the engine skip the sort entirely — the same composite-index logic as general query optimization.
• A window function almost always beats the correlated subquery it replaces, which re-executes per row; verify with EXPLAIN / EXPLAIN ANALYZE using the online EXPLAIN reader or your client.
• Reduce input rows with WHERE first — window functions see whatever survives filtering, so early filtering shrinks every partition.

If you are stuck on a legacy MySQL 5.7 box, generate test data with the SQL data generator and prototype the window version on any MySQL 8 sandbox before committing to the rewrite.

FAQ — SQL Window Functions