Window Functions

Advanced

⏱️ 25 mins read

What You'll Learn

Window functions perform calculations across rows related to the current row WITHOUT collapsing them like GROUP BY. They use the OVER() clause with optional PARTITION BY (grouping) and ORDER BY (ordering within partition). Key functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG, FIRST_VALUE, LAST_VALUE.

Syntax

FUNCTION() OVER (
  PARTITION BY col
  ORDER BY col
  ROWS BETWEEN ... AND ...
)

Example

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT name, salary, department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

-- LAG / LEAD: access previous/next rows
SELECT
  order_date,
  revenue,
  LAG(revenue)  OVER (ORDER BY order_date) AS prev_revenue,
  LEAD(revenue) OVER (ORDER BY order_date) AS next_revenue,
  revenue - LAG(revenue) OVER (ORDER BY order_date) AS day_change
FROM daily_sales;

-- Running total
SELECT order_date, revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales;

Common Mistakes

Confusing RANK and DENSE_RANK: RANK gives 1,2,2,4 (skips 3). DENSE_RANK gives 1,2,2,3 (no skip). ROW_NUMBER always gives unique sequential numbers regardless of ties. Window functions cannot be used in WHERE — wrap in a CTE or subquery.

Interview Tips

Window functions are the #1 topic at FAANG-level SQL interviews. Know ROW_NUMBER for deduplication (keep latest row), RANK/DENSE_RANK for top-N per group, LAG/LEAD for period-over-period comparisons, and running totals.

Practice

Find the top 3 products by revenue in each category (use DENSE_RANK for ties). Also calculate the 7-day moving average of daily revenue.

Back to Full Roadmap