ROW_NUMBER, RANK, DENSE_RANK, NTILE
Ranking window functions assign a position to each row within a partition without collapsing rows. ROW_NUMBER assigns unique sequential numbers. RANK skips numbers after ties. DENSE_RANK doesn't skip. NTILE divides rows into N equal buckets.
RANK is the Olympics — tied for gold? Both get gold (#1), nobody gets silver (#2), bronze is #3. DENSE_RANK is a class rank — tied for first, both are #1, next student is #2 (no skipped ranks). ROW_NUMBER is attendance — regardless of ties, each person gets a unique number.
ROW_NUMBER: always unique (1, 2, 3...). RANK: ties get the same rank, next rank skips (1, 1, 3). DENSE_RANK: ties get same rank, next rank doesn't skip (1, 1, 2). NTILE(4): assigns rows to quartiles (1–4). Syntax: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC). PARTITION BY resets the numbering per group; ORDER BY defines ranking order. Without PARTITION BY, all rows are one partition.
ROW_NUMBER is deterministic only if ORDER BY is unique. With ties in ORDER BY (same salary, same department), the row number assignment is arbitrary — add a tiebreaker (id column) for determinism. RANK and DENSE_RANK are more appropriate when ties are meaningful (leaderboards). PERCENT_RANK returns (rank - 1) / (count - 1) as a fraction 0–1. CUME_DIST returns fraction of rows with value ≤ current row. For top-N per group queries, ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC) with WHERE row_num <= N in a subquery or CTE is the canonical pattern.
I pick ROW_NUMBER for top-N per group (guaranteed unique ranks), RANK for competition leaderboards where ties share rank (and next rank skips), DENSE_RANK when ties share rank but you don't want gaps. NTILE for quartile/percentile bucketing. The key pattern for top-N per group: wrap the window function in a CTE or subquery, then filter WHERE rn <= N.
ROW_NUMBER with a non-deterministic ORDER BY (ties in the sort key) produces unpredictable, inconsistent rankings across query runs. Always add a unique tiebreaker (primary key) to the ORDER BY for deterministic results.