SQL Window Functions — ROW_NUMBER, RANK, LAG, and More

Window functions perform calculations across a set of table rows that are related to the current row. Unlike GROUP BY, they don’t collapse rows — you keep all original rows while adding computed values.

Use the SQL Formatter to format and highlight your SQL queries.

Window function syntax

function_name() OVER (
  [PARTITION BY column_list]
  [ORDER BY column_list]
  [frame_clause]
)

• PARTITION BY — divide rows into groups (like GROUP BY but keeps rows)
• ORDER BY — determines row order within each partition
• Frame clause — specifies which rows relative to current are included

ROW_NUMBER

Assigns a sequential integer to each row within a partition:

SELECT
  id,
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Results:

id | name    | department | salary | global_rank | dept_rank
---+---------+------------+--------+-------------+-----------
 1 | Alice   | Eng        | 120000 |           1 |         1
 2 | Bob     | Eng        | 110000 |           2 |         2
 3 | Carol   | Sales      | 95000  |           3 |         1
 4 | Dave    | Sales      | 88000  |           4 |         2

RANK and DENSE_RANK

SELECT
  name,
  salary,
  RANK()       OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Difference when there are ties:

• RANK: 1, 2, 2, 4 (skips 3 after a tie)
• DENSE_RANK: 1, 2, 2, 3 (no gaps)

Get top N per group (common problem)

Use ROW_NUMBER to select the top salary per department:

-- Top 2 earners per department:
WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT *
FROM ranked
WHERE rn <= 2;

LAG and LEAD

Access values from previous or next rows:

SELECT
  date,
  revenue,
  LAG(revenue, 1)  OVER (ORDER BY date) AS prev_revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

LAG(expr, offset, default) — look back offset rows LEAD(expr, offset, default) — look forward offset rows

Running totals with SUM OVER

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day
FROM daily_sales;

Frame clauses

-- All rows from start to current (running total):
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Last 3 rows and current (rolling 4-row window):
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

-- All rows in partition:
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- Range (by value, not row count):
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

FIRST_VALUE and LAST_VALUE

SELECT
  name,
  department,
  salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name)  OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;

Note: LAST_VALUE needs an explicit frame clause to reach the end; default frame stops at current row.

NTILE

Divides rows into n buckets:

SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- 1 = top 25%, 4 = bottom 25%

Percent rank and cumulative distribution

SELECT
  name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST()    OVER (ORDER BY salary) AS cume_dist
FROM employees;
-- PERCENT_RANK: 0 to 1 (where does this row rank relative to all rows?)
-- CUME_DIST: fraction of rows at or below this value

Month-over-month growth

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) /
    LAG(revenue) OVER (ORDER BY month) * 100,
    2
  ) AS pct_change
FROM monthly_revenue;

Related tools

• SQL Formatter — format and highlight SQL
• SQL Joins Guide — INNER, LEFT, RIGHT, FULL joins
• SQL Query Optimization — indexing and performance