Window Functions

Window Functions are the "Swiss Army Knife" of T-SQL. They allow you to perform calculations across a set of table rows that are related to the current row, without collapsing the rows into a single result (unlike GROUP BY).

1. ROW_NUMBER vs RANK vs DENSE_RANK

• ROW_NUMBER(): Unique sequential number for each row. (Perfect for Pagination).
• RANK(): Skips numbers if there is a tie. (1, 2, 2, 4).
• DENSE_RANK(): Doesn't skip numbers if there is a tie. (1, 2, 2, 3).

SELECT Name, Salary, 
       RANK() OVER(ORDER BY Salary DESC) as Rank
FROM Employees

2. Analytic Functions (LEAD/LAG)

How do you compare 'Today's Sales' with 'Yesterday's Sales' in one row? You use LAG() to peek at the previous row. This eliminates the need for expensive self-joins.

4. Interview Mastery

Q: "What is the purpose of the 'PARTITION BY' clause in a window function?"

Architect Answer: "It is like a 'Local Group By'. Instead of ranking everyone in the company, `PARTITION BY DepartmentID` will reset the ranking for every department. Department A will have a Rank 1, and Department B will also have a Rank 1. It allows you to group logic while still seeing every individual row."