foundations • Intermediate

Window Functions (ROW_NUMBER, RANK, OVER PARTITION)

Window functions perform calculations across a set of table rows related to the current row without collapsing them into a single row like GROUP BY does.

Intuitive Mental Model

The Rolling Leaderboard: Every runner crosses the finish line and retains their individual bib number and name, while the digital screen calculates their rank and time gap relative to their specific age division.

SQL DDL / DML QueryPostgreSQL / MySQL

-- Top 3 Highest Earners Per Department:
WITH RankedSalaries AS (
  SELECT 
    id,
    name,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department 
      ORDER BY salary DESC
    ) as rank_in_dept,
    AVG(salary) OVER (
      PARTITION BY department
    ) as dept_avg_salary
  FROM employees
)
SELECT * FROM RankedSalaries WHERE rank_in_dept <= 3;

Key Architectural Takeaways

•ROW_NUMBER() assigns consecutive integers (1, 2, 3, 4).
•RANK() leaves gaps for ties (1, 2, 2, 4); DENSE_RANK() does not leave gaps (1, 2, 2, 3).
•LAG() and LEAD() fetch values from preceding or succeeding rows without self-joins (ideal for calculating month-over-month growth).

Common Production Mistake

Trying to use window functions directly inside WHERE (e.g. WHERE ROW_NUMBER() = 1), causing a syntax error.