Can you tell me the difference between RANK() and DENSE_RANK()?

RANK() and DENSE_RANK() are both window functions that assign a rank to rows within a partition based on an ORDER BY clause, but they handle ties differently. RANK() assigns the same rank to rows with identical values, but leaves gaps in the ranking sequence for subsequent rows. For example, if two rows tie for rank 1, the next distinct value receives rank 3. DENSE_RANK() also assigns the same rank to tied rows, but it does not create gaps; the next distinct value receives the next consecutive rank. This difference matters when you need a continuous ranking sequence. Use RANK() when you want to preserve the ordinal position of ties (e.g., leaderboard positions where ties skip numbers). Use DENSE_RANK() when you need a compact ranking without gaps (e.g., grouping items into tiers). Both functions require a window definition: PARTITION BY to group rows and ORDER BY to determine the ranking order. They are commonly used in reporting, analytics, and when selecting top N rows per group.