8. Window functions

SELECT FullName, DeptId, Salary,
    ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Salary DESC)  AS RowNum,     -- 1,2,3,4
    RANK()       OVER (PARTITION BY DeptId ORDER BY Salary DESC)  AS Rnk,        -- 1,2,2,4
    DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC)  AS DenseRnk,   -- 1,2,2,3
    NTILE(4)     OVER (ORDER BY Salary)                            AS Quartile,
    SUM(Salary)  OVER (PARTITION BY DeptId)                        AS DeptPayroll,
    Salary * 100.0 / SUM(Salary) OVER (PARTITION BY DeptId)        AS PctOfDept,
    LAG(Salary)  OVER (ORDER BY HireDate)                          AS PrevSalary,
    LEAD(Salary) OVER (ORDER BY HireDate)                          AS NextSalary,
    SUM(Salary)  OVER (ORDER BY HireDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
    AVG(Salary)  OVER (ORDER BY HireDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS MovingAvg3,
    FIRST_VALUE(FullName) OVER (PARTITION BY DeptId ORDER BY Salary DESC)                  AS TopEarner
FROM Employees;

WITH r AS (SELECT *, DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS dr FROM Employees)
SELECT * FROM r WHERE dr = 1;     -- top earner(s) per department