179 questions
No questions match those filters.
ROW_NUMBER, RANK, and DENSE_RANK all rank rows — what's...
This is one of the questions in the full AI/ML interview bank. Pro unlocks all 1789 questions; Premium includes the same bank plus the highest daily Practice limit.
See plansThe standard deduplication pattern is worth memorizing verbatim, because it comes up constantly in real data work: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY signup_date) AS rn FROM customers) SELECT * FROM ranked WHERE rn = 1 keeps exactly one row per email — the earliest by signup_date — and WHERE rn > 1 isolates the duplicates for review or deletion. This works precisely because ROW_NUMBER() never ties: even two rows with identical signup_date get distinct numbers, so rn = 1 always returns exactly one row per partition, never zero and never two.
RANK() and DENSE_RANK() matter when ties are semantically meaningful rather than an artifact to break arbitrarily — a leaderboard, a scoring competition, a “top 3 by revenue” report where two products genuinely tied for second place should both display as “2nd.” The gap RANK() introduces after a tie (1, 1, 3, 4) mirrors how sports rankings actually work, which is intuitive for competition-style reporting but wrong for anything downstream that expects a contiguous sequence, like paginating “page 2 = ranks 11–20,” where a run of ties can silently shift which rows land on which page.
The failure that’s easy to miss in code review: someone uses RANK() for a top-N-per-group query expecting exactly N rows per group, but a tie at the boundary produces more than N rows for some groups and skips a rank number for others. If the requirement is “exactly one row per group, tie or not,” ROW_NUMBER() with a deterministic secondary ORDER BY (to make the tie-break reproducible, not arbitrary) is the only one of the three that satisfies it.