A free, self-paced textbook in 6 chapters. Read a chapter, then drill it with the 223 companion flashcards using spaced repetition.
A window function is computed across a set of rows related to the current row without collapsing the result set, which is the key distinction from a GROUP BY aggregation. Its gener...
The ranking family includes ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST. ROW_NUMBER assigns a unique sequential number to each row within a partition, making i...
The value family — LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE — fetches values from other rows relative to the current one within the same partition. LAG(col, n, default) re...
Standard aggregates — SUM, AVG, COUNT, MIN, MAX, and their statistical variants — become window functions when used with an OVER clause, returning one value per row without collaps...
Window functions unlock a remarkable range of analytical patterns. Sessionization — splitting a user's event stream into sessions based on a 30-minute inactivity gap — is achieved...
Performance hinges on a few guiding principles. Composite indexes on the partition and order columns let the engine avoid a Sort step — EXPLAIN plans in PostgreSQL show a WindowAgg...