How to Use Window Functions in SQL (Complete Guide)

Introduction

Window functions perform calculations across a set of rows related to the current row — without collapsing them into a single output like GROUP BY. They are the most powerful feature in modern SQL.

Window Function Syntax

Every window function follows the same pattern: function() OVER (PARTITION BY ... ORDER BY ...).

-- The basic pattern:
SELECT
  name,
  department,
  salary,
  function_name() OVER (
    PARTITION BY department  -- groups (optional)
    ORDER BY salary DESC     -- sort within group
  ) AS result
FROM employees;

-- PARTITION BY = like GROUP BY but without collapsing rows
-- ORDER BY = sort order within each partition
-- Both are optional depending on the function

ROW_NUMBER, RANK, DENSE_RANK

Ranking functions assign position numbers to rows within each partition.

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

-- Results for tied salaries ($80K, $80K, $70K):
-- ROW_NUMBER: 1, 2, 3  (always unique)
-- RANK:       1, 1, 3  (ties get same rank, next rank skipped)
-- DENSE_RANK: 1, 1, 2  (ties get same rank, next rank NOT skipped)

LAG and LEAD (Previous/Next Row)

Access data from previous (LAG) or next (LEAD) rows without self-joins.

-- Compare each month's revenue to the previous month:
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / LAG(revenue, 1) OVER (ORDER BY month), 1
  ) AS pct_change
FROM monthly_revenue;

-- Look ahead with LEAD:
SELECT
  name, hire_date,
  LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire_date
FROM employees;

Running Total (SUM OVER)

Calculate cumulative sums that grow as you move through the rows.

-- Running total of revenue:
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Running total per category:
SELECT
  date, category, amount,
  SUM(amount) OVER (
    PARTITION BY category
    ORDER BY date
  ) AS category_running_total
FROM transactions;

Moving Average

Calculate averages over a sliding window of N rows.

-- 7-day moving average:
SELECT
  date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM daily_stats;

-- 3-row moving average (centered):
SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_avg
FROM measurements;

FIRST_VALUE, LAST_VALUE, NTH_VALUE

Get specific values from the window frame.

-- Compare each salary to the highest in the department:
SELECT
  name, department, salary,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS highest_salary,
  salary - FIRST_VALUE(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS diff_from_highest
FROM employees;

Best Practices

• Use named window definitions (WINDOW w AS ...) when reusing the same OVER clause
• Understand the default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Use ROWS (not RANGE) for moving averages to get exact N-row windows
• Window functions run after WHERE and GROUP BY but before ORDER BY
• Combine multiple window functions in one query for complex analytics

Generate SQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL query instantly.

Frequently Asked Questions