ROW_NUMBER, RANK and DENSE_RANK differ where it matters

All three number rows within a partition and they disagree about ties, which is invisible in test data where nothing ties and obvious in production where everything does.

SELECT sku, sales,
  ROW_NUMBER() OVER w AS rn,    -- 1,2,3,4 — arbitrary among ties
  RANK()       OVER w AS rk,    -- 1,2,2,4 — gaps after a tie
  DENSE_RANK() OVER w AS dr     -- 1,2,2,3 — no gaps
FROM sales
WINDOW w AS (PARTITION BY category ORDER BY sales DESC);

ROW_NUMBER is the one for pagination and for picking exactly one row per group, and its arbitrariness among ties means the result is not deterministic unless the ORDER BY breaks them — adding the primary key as a final sort column is the fix and it costs nothing. RANK is what a leaderboard means. DENSE_RANK is what “top three price tiers” means, and choosing the wrong one produces a report that is quietly incorrect rather than obviously broken.