Window functions perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row.

FUNCTION() OVER (PARTITION BY col1 ORDER BY col2)

• PARTITION BY: Divides the result set into partitions (groups).
• ORDER BY: Ranks the rows within each partition.

• ROW_NUMBER(): Assigns a unique sequential number to rows (e.g., 1, 2, 3, 4).
• RANK(): Assigns the same rank to ties, but leaves gaps (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Assigns the same rank to ties, but no gaps (e.g., 1, 2, 2, 3).

sql Standard
-- Find top 3 highest-paid employees per department
SELECT * FROM (
    SELECT 
        Name, 
        Department, 
        Salary,
        DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as SalaryRank
    FROM Employees
) RankedEmployees
WHERE SalaryRank <= 3;

• LAG(): Accesses data from a previous row.
• LEAD(): Accesses data from a subsequent row.
• FIRST_VALUE() / LAST_VALUE(): Returns the value from the first/last row of the window.

• Missing ORDER BY: Forgetting the ORDER BY clause in OVER() when using ranking functions (makes the order non-deterministic).
• Aggregate vs Window: Trying to use GROUP BY with window functions when you need individual row details.
• Complexity: Writing nested window functions without understanding the performance impact on large datasets.