SQL Window Functions Explained: ROW_NUMBER, RANK, LAG, LEAD and More

Window functions are one of the most powerful features in SQL — and one of the most asked-about in data and backend engineering interviews. They let you perform calculations across a set of rows related to the current row, without collapsing the result into a single value like GROUP BY does.

What Is a Window Function?

A regular aggregate function like SUM() or COUNT() collapses many rows into one. A window function performs a calculation across a set of rows (the "window") but keeps all the original rows in the result.

sql
-- Aggregate: collapses rows
SELECT department, SUM(salary) FROM employees GROUP BY department;

-- Window function: keeps all rows, adds running context
SELECT
  name,
  department,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

The second query returns every employee row AND adds the total salary for their department — without collapsing anything.

The OVER Clause

Every window function uses OVER() to define the window:

sql
function_name() OVER (
  PARTITION BY column    -- divide rows into groups (optional)
  ORDER BY column        -- order within the partition (optional)
  ROWS/RANGE frame       -- define which rows to include (optional)
)

Ranking Functions

ROW_NUMBER

Assigns a unique sequential number to each row within its partition:

sql
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

name	department	salary	row_num
Alice	Engineering	120000	1
Bob	Engineering	110000	2
Carol	Engineering	95000	3
Dave	Marketing	85000	1
Eve	Marketing	80000	2

Use case: Get the top-N rows per group (e.g., the top 3 earners per department):

sql
WITH ranked AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

RANK and DENSE_RANK

Both handle ties — RANK leaves gaps, DENSE_RANK does not:

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

name	salary	rank	dense_rank
Alice	120000	1	1
Bob	110000	2	2
Carol	110000	2	2
Dave	95000	4	3

RANK skips 3 because two people tied at rank 2. DENSE_RANK never skips.

LAG and LEAD

Access the value from a previous or next row without a self-join:

sql
SELECT
  order_date,
  revenue,
  LAG(revenue, 1)  OVER (ORDER BY order_date) AS prev_day_revenue,
  LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS day_over_day_change
FROM daily_sales
ORDER BY order_date;

The optional second argument is the offset (default 1). A third argument provides a default when there is no previous/next row:

sql
LAG(revenue, 1, 0) OVER (ORDER BY order_date) -- returns 0 if no previous row

Use case: Month-over-month growth rate:

sql
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 growth_pct
FROM monthly_revenue;

Running Totals and Moving Averages

Control exactly which rows are included using the frame clause:

sql
-- Running total (cumulative sum)
SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

-- 7-day moving average
SELECT
  order_date,
  amount,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM daily_sales;

-- Moving average including future rows (centered)
AVG(amount) OVER (
  ORDER BY order_date
  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
) AS centered_avg

FIRST_VALUE and LAST_VALUE

Return the first or last value in the window frame:

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

LAST_VALUE requires an explicit frame (UNBOUNDED FOLLOWING) because the default frame only goes to the current row.

NTILE

Divide rows into N equal buckets:

sql
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
-- 1 = bottom 25%, 2 = 25-50%, 3 = 50-75%, 4 = top 25%

PERCENT_RANK and CUME_DIST

sql
SELECT
  name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  -- 0.0 for lowest, 1.0 for highest
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist
  -- fraction of rows with salary <= current row
FROM employees;

Practical Example: Cohort Analysis

Find each customer's first purchase date and days since first purchase:

sql
SELECT
  customer_id,
  order_date,
  amount,
  FIRST_VALUE(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_date,
  order_date - FIRST_VALUE(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS days_since_first_order,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_number
FROM orders;

Common Interview Questions

Q: What is the difference between GROUP BY and window functions?

GROUP BY aggregates multiple rows into one — you lose the individual row data. Window functions compute across related rows but return a result for every row in the original set. You can mix them: GROUP BY first, then window functions on the aggregated result.

Q: What is the difference between RANK and DENSE_RANK?

Both assign ranks with ties getting the same rank. RANK skips the next rank number after a tie (1, 2, 2, 4). DENSE_RANK never skips (1, 2, 2, 3).

Q: When would you use ROW_NUMBER vs RANK?

Use ROW_NUMBER when you need unique sequential numbers regardless of ties — for example, picking exactly one row per group (deduplication). Use RANK or DENSE_RANK when ties should share the same rank — for example, a leaderboard.

Practice SQL on Froquiz

Window functions are tested in data engineering, backend, and analytics interviews. Test your SQL knowledge on Froquiz across beginner, intermediate, and advanced levels.

Summary

• Window functions compute across related rows without collapsing the result
• OVER (PARTITION BY ... ORDER BY ...) defines the window
• ROW_NUMBER — unique sequential numbers; RANK — ties get same rank with gaps; DENSE_RANK — ties with no gaps
• LAG / LEAD — access previous or next row values without self-joins
• Frame clause (ROWS BETWEEN ...) controls which rows are included — essential for running totals and moving averages
• FIRST_VALUE / LAST_VALUE — first or last value in the window
• NTILE(N) — divide rows into N equal buckets