SQL Window Functions

Window functions let you perform calculations across rows related to the current row, without collapsing the result like GROUP BY does. They’re great for ranking, running totals, and more.

🔹 Key Window Functions

• ROW_NUMBER() — assigns a unique sequential number to rows in a partition
• RANK() — ranks rows, with gaps for ties
• DENSE_RANK() — ranks rows without gaps
• NTILE(n) — divides rows into n groups
• LAG() / LEAD() — access previous/next row’s data

🔹 Basic Syntax

SELECT
  column1,
  ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_num,
  RANK() OVER (ORDER BY order_column DESC) AS rank
FROM table_name;

• PARTITION BY divides data into groups (like GROUP BY, but rows remain visible)
• ORDER BY defines row order within each partition

🔹 Example: Ranking Employees by Salary in Each Department

SELECT
  employee_name,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

• ROW_NUMBER assigns unique rank
• RANK assigns same rank to ties, skips numbers after ties

🧠 Quick Recap

💡 Window functions power up your queries with advanced analytics — all while keeping rows intact!