SQL Window Functions Explained

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

Window functions let you calculate something "across" a set of related rows without collapsing them into one — they're one of the most powerful tools in SQL once they click, and one of the most confusing before they do.

📘 The Shared Pattern

Every window function follows the same basic shape:

function_name() OVER (
  PARTITION BY column   -- optional: split into groups
  ORDER BY column        -- optional: define row order within each group
)

🧪 ROW_NUMBER — a unique sequential number

SELECT
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Every row gets a unique number, 1, 2, 3... within its department, ordered by salary descending. No ties — even if two people have identical salaries, one gets 1 and the other gets 2, arbitrarily.

🧪 RANK vs DENSE_RANK — handling ties

SELECT
  employee_name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank_with_gaps,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no_gaps
FROM employees;

If two employees tie for 2nd place: RANK gives them both "2", then skips to "4" for the next person (leaving a gap). DENSE_RANK also gives them both "2", but the next person gets "3" (no gap). Which one you want depends on whether the gap is meaningful for your use case (e.g., "top 3 salaries" behaves differently with each).

🧪 LAG and LEAD — looking at neighboring rows

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

LAG pulls the value from a previous row; LEAD pulls from a following row — both without needing a self-join. This is exactly how you calculate day-over-day or month-over-month change directly in SQL.

🎯 A Practical Combined Example

Finding the top 3 highest-paid employees per department:

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

This is a genuinely common real-world pattern — "top N per group" — and window functions make it a clean, single query instead of a messy correlated subquery.

⚠️ Common Mistakes

• Forgetting that window functions run after WHERE and GROUP BY — you can't filter directly on a window function's result in the same SELECT (hence the "wrap it in a subquery" pattern above)
• Using ROW_NUMBER when RANK or DENSE_RANK is actually semantically correct for the data (e.g., a leaderboard with genuine ties)
• Forgetting ORDER BY inside OVER() for LAG/LEAD — without it, "previous row" is undefined and results become unpredictable

🔗 Related Posts

• GROUP BY vs PARTITION BY in SQL — What's the Difference?
• Common Table Expressions (CTEs) in SQL — A Beginner's Guide