SQL Window Functions Tutorial: ROW_NUMBER, RANK, LAG, LEAD

Window functions are the most powerful feature in SQL that most developers never learn. They let you perform calculations across rows without collapsing them into a single result — perfect for ranking, running totals, and time-series analysis. Here's everything you need to know.

What Are Window Functions?

A window function performs a calculation across a set of rows related to the current row. Unlike GROUP BY, which collapses rows, window functions keep all rows intact and add a calculated column.

Example: Rank employees by salary within each department, but keep all employee rows visible.

SELECT
  employee_id,
  name,
  salary,
  department,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Result: Every employee row is returned, plus a new salary_rank column showing their rank within their department.

Core Window Functions

ROW_NUMBER() — Unique Sequential Number

Assigns a unique number to each row within a partition, ordered by a column.

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_sequence
FROM orders;

Result: Each customer's orders are numbered 1, 2, 3... in reverse chronological order. Even if two orders have the same amount, they get different row numbers.

RANK() — Rank with Ties

Like ROW_NUMBER(), but rows with the same value get the same rank. The next rank skips numbers.

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM test_scores;

-- Result:
-- Alice  95  1
-- Bob    95  1
-- Charlie 90 3  (note: rank 2 is skipped)
-- Diana  85  4

DENSE_RANK() — Rank Without Gaps

Like RANK(), but ranks are consecutive with no gaps.

SELECT
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM test_scores;

-- Result:
-- Alice  95  1
-- Bob    95  1
-- Charlie 90 2  (no gap)
-- Diana  85  3

LAG() & LEAD() — Access Previous/Next Rows

LAG() accesses the previous row. LEAD() accesses the next row. Perfect for calculating differences or trends.

SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS previous_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day_change,
  LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
FROM daily_sales
ORDER BY date;

Result: Each row shows today's revenue, yesterday's revenue, the change, and tomorrow's revenue.

SUM() / AVG() / COUNT() OVER — Running Totals

Aggregate functions with OVER create running totals or moving averages.

SELECT
  date,
  sales,
  SUM(sales) OVER (ORDER BY date) AS cumulative_sales,
  AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7day
FROM daily_sales
ORDER BY date;

Result: cumulative_sales grows each day. moving_avg_7day shows the 7-day rolling average.

PARTITION BY vs ORDER BY

PARTITION BY divides rows into groups (like GROUP BY).ORDER BY determines the order within each partition.

-- Rank employees by salary WITHIN each department
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

-- Rank employees by salary ACROSS the entire company
SELECT
  name,
  department,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS company_salary_rank
FROM employees;

Real-World Examples

Example 1: Find Top 3 Products per Category

WITH ranked_products AS (
  SELECT
    category,
    product_name,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
  FROM products
)
SELECT *
FROM ranked_products
WHERE rank <= 3;

Example 2: Detect Churn (Users with No Activity for 30 Days)

WITH user_activity AS (
  SELECT
    user_id,
    last_activity_date,
    LEAD(last_activity_date) OVER (PARTITION BY user_id ORDER BY last_activity_date) AS next_activity,
    DATEDIFF(
      LEAD(last_activity_date) OVER (PARTITION BY user_id ORDER BY last_activity_date),
      last_activity_date
    ) AS days_until_next_activity
  FROM user_events
)
SELECT user_id, last_activity_date
FROM user_activity
WHERE days_until_next_activity > 30 OR next_activity IS NULL;

Example 3: Cohort Analysis (Retention by Signup Month)

SELECT
  DATE_TRUNC('month', signup_date) AS cohort_month,
  DATE_TRUNC('month', last_activity_date) AS activity_month,
  COUNT(DISTINCT user_id) AS active_users,
  DATEDIFF(MONTH, DATE_TRUNC('month', signup_date), DATE_TRUNC('month', last_activity_date)) AS months_since_signup
FROM users
GROUP BY cohort_month, activity_month
ORDER BY cohort_month, activity_month;

Common Pitfalls

Pitfall 1: Forgetting ORDER BY

Window functions without ORDER BY process rows in arbitrary order. Always specify ORDER BY unless you explicitly want unordered processing.

Pitfall 2: Mixing Window Functions with GROUP BY

GROUP BY collapses rows; window functions don't. You can't use both on the same column without a CTE.

Pitfall 3: Not Understanding Frame Specification

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines which rows are included in the calculation. Omitting it defaults to all rows from the start to the current row.

Frequently Asked Questions

Are window functions supported in all databases?

Most modern databases support them: PostgreSQL, MySQL 8.0+, SQL Server, BigQuery, Snowflake. SQLite has limited support. Check your database documentation.

How do window functions affect query performance?

Window functions are generally fast, but they require sorting and can be expensive on large datasets. Use indexes on PARTITION BY and ORDER BY columns to optimize.

Can I use multiple window functions in one query?

Yes. You can have multiple OVER clauses in the same query. Each one operates independently.

The Bottom Line

Window functions are the bridge between simple queries and complex analytics. Once you understand PARTITION BY, ORDER BY, and the core functions (ROW_NUMBER, RANK, LAG, LEAD), you can solve problems that would otherwise require multiple queries or application-level logic.

Start with simple examples. Build up to complex cohort analysis and time-series queries. Your data analysis will never be the same.