Window Functions

After this lesson, you will be able to: Use window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, with OVER + PARTITION BY.

Window functions appear in nearly every data-engineering interview. They calculate values across a 'window' of rows without collapsing the result like GROUP BY does.

Prerequisites:Subqueries and CTEs

ROW_NUMBER + RANK + DENSE_RANK

Number rows within partitions.

sql

-- Rank posts within each user by created_at
SELECT id, user_id, title, created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS user_rank
FROM posts;

-- Common pattern: 'most recent post per user'
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM posts
)
SELECT id, user_id, title, created_at FROM ranked WHERE rn = 1;

-- RANK vs DENSE_RANK
-- ROW_NUMBER: 1, 2, 3, 4 (always unique)
-- RANK:       1, 1, 3, 4 (ties get same rank; next rank skips)
-- DENSE_RANK: 1, 1, 2, 3 (ties get same rank; no skip)

LAG + LEAD — peek at neighboring rows

Reference previous/next row in the window.

sql

-- Daily revenue + previous-day revenue + day-over-day delta
SELECT day, revenue,
  LAG(revenue) OVER (ORDER BY day) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY day) AS delta
FROM daily_revenue;

-- LEAD looks ahead
SELECT day, revenue, LEAD(revenue) OVER (ORDER BY day) AS next_day
FROM daily_revenue;

Running totals + moving averages

Use SUM/AVG with a window frame.

sql

-- Cumulative revenue
SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

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

-- Partitioned running total per user
SELECT user_id, day, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY day) AS running_total
FROM transactions;

Quick Check

Given values [10, 10, 20], what do ROW_NUMBER, RANK, DENSE_RANK return?

Pick the right combination.

All three return [1, 2, 3]ROW_NUMBER [1, 2, 3]; RANK [1, 1, 3]; DENSE_RANK [1, 1, 2]ROW_NUMBER [1, 1, 2]; RANK [1, 2, 3]; DENSE_RANK [1, 2, 3]All three return [1, 1, 2]

Common mistakes

Forgetting ORDER BY inside OVER for ordered windows (results become indeterminate). Confusing PARTITION BY with GROUP BY (different scope). Not specifying window frame for SUM/AVG (defaults can surprise, RANGE vs ROWS). Window functions in WHERE (illegal, must wrap in CTE first).

←Subqueries and CTEs

Back to SQL

Indexes and Query Performance→