Window Functions

Learn SQL window functions with practical examples and guided drills on dbSyntax.

Compute rankings, running totals, and moving metrics without collapsing rows. A window function sees a group of rows (the "window") and retu...

ROW_NUMBER

Assign a unique sequential number to each row within a partition, in a defined order. The workhorse window function — powers top-N-per-group...

Rank rows by an ordered metric. Ties share a rank, and the next rank skips the count of ties (1, 2, 2, 4). Use RANK when "tied for 2nd, nobo...

Rank rows without gaps: ties share a rank, and the next rank is always the next integer (1, 2, 2, 3). Use DENSE_RANK when you care which buc...

Read a value from the previous (LAG) or next (LEAD) row within a partition, in a defined order. The backbone of row-over-row metrics: month-...

Split ordered rows into N equal-sized buckets: quartiles (NTILE(4)), deciles (NTILE(10)), percentiles (NTILE(100)). If the row count doesn't...

Return the first or last value from a window frame. FIRST_VALUE with the default frame does what you'd expect. LAST_VALUE almost never does....