Set your Publisher ID in src/config/ads.ts to display live ads.
Window Functions: ROW_NUMBER, RANK & OVER
PARTITION BY, ORDER BY frames, LAG, LEAD, and running totals
Unlike GROUP BY which collapses rows, Window Functions perform calculations across sets of rows related to the current row while retaining individual row identities.
The Anatomy of the OVER() Clause
PARTITION BY: Divides rows into analytical windows (similar to GROUP BY, but without row collapsing).
ORDER BY: Dictates the sequence of row evaluation inside the window frame.
ROW_NUMBER(): Assigns unique sequential integers (1, 2, 3...) per partition.
RANK() vs DENSE_RANK(): Assigns rankings with or without gaps for tied values.
LAG(col, 1) and LEAD(col, 1): Accesses data from previous or subsequent rows without self-joins, essential for month-over-month growth calculations.
WITH RankedEmployees AS (
SELECT
EmployeeName,
Department,
Salary,
DENSE_RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS DeptRank
FROM Employees
)
SELECT EmployeeName, Department, Salary, DeptRank
FROM RankedEmployees
WHERE DeptRank <= 3;Set your Publisher ID in src/config/ads.ts to display live ads.
Test Yourself With Exercise
What is the key difference between `GROUP BY` and a Window Function with `OVER(PARTITION BY ...)`?