SQL Window Functions Tutorial: ROW_NUMBER, RANK, LAG, and Running Totals

Overview

Window functions compute values across a set of rows related to the current row, without collapsing those rows like GROUP BY does. They are essential for ranking, running totals, and comparing a row to its neighbors. This tutorial uses a consistent dataset to demonstrate each function.

Sample Data

-- sales
+----+---------+--------+------------+
| id | region  | amount | sale_date  |
+----+---------+--------+------------+
| 1  | EU      | 100    | 2026-01-05 |
| 2  | EU      | 250    | 2026-01-12 |
| 3  | EU      | 180    | 2026-02-03 |
| 4  | US      | 300    | 2026-01-08 |
| 5  | US      | 150    | 2026-01-20 |
| 6  | US      | 400    | 2026-02-11 |
+----+---------+--------+------------+

Window Function Syntax

function_name(...) OVER (
  [PARTITION BY column]
  [ORDER BY column]
  [frame_clause]
)

Clause	Purpose
`PARTITION BY`	Split rows into groups; the function restarts for each group
`ORDER BY`	Define the order within each partition
Frame clause	Restrict which rows the function sees (e.g., `ROWS BETWEEN ...`)

Ranking Functions

SELECT
  id,
  region,
  amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rank_val,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank_val
FROM sales;

Function	Behavior on ties
`ROW_NUMBER`	Always unique, ties broken arbitrarily
`RANK`	Ties share a rank; next rank skips (1, 1, 3)
`DENSE_RANK`	Ties share a rank; next rank does not skip (1, 1, 2)

Running Totals

SELECT
  id,
  region,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales
ORDER BY region, sale_date;

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW restricts the sum to all rows up to the current one within the partition.

Comparing to Previous and Next Rows

SELECT
  id,
  sale_date,
  amount,
  LAG(amount)  OVER (ORDER BY sale_date) AS prev_amount,
  LEAD(amount) OVER (ORDER BY sale_date) AS next_amount,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS change
FROM sales
ORDER BY sale_date;

LAG and LEAD accept optional arguments for offset and default:

LAG(amount, 1, 0) OVER (ORDER BY sale_date)

Moving Averages

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM sales;

First and Last Values in a Partition

SELECT
  id,
  region,
  amount,
  FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date) AS first_sale,
  LAST_VALUE(amount)  OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_sale
FROM sales;

LAST_VALUE needs an explicit frame ending at UNBOUNDED FOLLOWING; otherwise it defaults to the current row.

Percent of Total

SELECT
  region,
  amount,
  ROUND(100.0 * amount / SUM(amount) OVER (), 2) AS pct_of_total
FROM sales;

An empty OVER () computes across all rows, giving the grand total.

Window Functions vs GROUP BY

Aspect	GROUP BY	Window function
Output rows	One per group	One per input row
Can mix with detail columns	No	Yes
Typical use	Summaries	Rankings, running totals, comparisons

Filtering on a Window Result

Window functions are evaluated after WHERE, so you cannot filter on them directly. Wrap the query in a subquery or CTE:

WITH ranked AS (
  SELECT
    id, region, amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn = 1;

Supported Databases

Window functions are supported in PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+, and BigQuery. Always check the version of your database before relying on them.