Explain the difference between RANK, DENSE_RANK and ROW_NUMBER.

All three assign a position within a window partition; they differ only on ties.

ROW_NUMBER always produces distinct sequential numbers, breaking ties arbitrarily. RANK gives tied rows the same rank and then skips — 1, 2, 2, 4. DENSE_RANK gives tied rows the same rank without skipping — 1, 2, 2, 3.

For a top-N-per-group query ROW_NUMBER is usually what you want, since it guarantees exactly N rows even with ties. Note the window cannot go in WHERE, so wrap it in a subquery or CTE and filter there.