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 general syntax follows the pattern function() OVER (PARTITION BY ... ORDER BY ... frame_clause), where PARTITION BY divides rows into independent groups analogous to a GROUP BY that does not collapse rows, and the ORDER BY inside the OVER clause defines the logical order within each partition used for ranking and frame evaluation. The frame clause then narrows which rows within the partition the function operates on, expressed using ROWS BETWEEN ... PRECEDING/FOLLOWING style bounds, and each window is applied independently within its partition.
Frames have important defaults that catch many beginners. When an OVER clause includes an ORDER BY, the default frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which yields a running total. Without an ORDER BY, the entire partition forms the frame, which is exactly what is needed for percent-of-total calculations. ROWS, RANGE, and GROUPS differ in subtle but important ways: ROWS counts physical row offsets, RANGE treats rows with the same ORDER BY value as a single logical position so peer rows share the same window endpoint, and GROUPS treats peer ties as units for frame definition. The optional EXCLUDE clause further refines frames with options like EXCLUDE CURRENT ROW, EXCLUDE GROUP, and EXCLUDE TIES, allowing fine-grained control over which rows participate.
Several behavioral rules deserve to be memorized. Windows are evaluated after WHERE but before the outer ORDER BY, so a window function cannot appear in a WHERE clause directly — the typical workaround is wrapping in a subquery, using a CTE, or employing QUALIFY where the engine supports it. The ORDER BY inside OVER is also independent of the query-level ORDER BY: the former orders the frame, while the latter orders the final result. Multiple windows sharing the same definition can be declared once via a named WINDOW clause, which improves readability and avoids repetition when computing several measures over the same partition and ordering.