SQL Ranking Functions Practice — 32 Challenges (10 Free)

ROW_NUMBER — exactly one row per position: 15 challenges (5 free)

Numbers rows 1, 2, 3 with no ties, even when two rows are equal. That is what you want for "the latest order per customer": ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC), then keep rank 1. Add a tie-breaker to the ORDER BY, or the row you keep is arbitrary.

Counted from the challenge bank, September 2026. Easy first, then Medium, then Hard.

RANK and DENSE_RANK — when ties matter: 14 challenges (6 free)

Two salaries of 90,000 are both second. RANK then skips to fourth; DENSE_RANK goes to third. "The second-highest salary" is a DENSE_RANK question, and saying why out loud is half the interview answer.

Counted from the challenge bank, September 2026. Easy first, then Medium, then Hard.

Top-N per group — rank inside a partition, then filter: 19 challenges (5 free)

The ranking runs in a CTE or a subquery, because a window function cannot sit in WHERE. Then the outer query keeps rn <= N. This is the shape behind "top sellers by region" and "best month per product".

Counted from the challenge bank, September 2026. Easy first, then Medium, then Hard.

Where this shows up next

Once rows are ranked, the next questions are about neighbours and running sums: window functions cover LAG, LEAD and frames. The filter-after-ranking step is a CTE or a subquery, and the rows being ranked usually come out of a join.

Questions

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

ROW_NUMBER gives every row a distinct number, ties included. RANK gives tied rows the same number and then skips (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3).

How do I get the top N rows per group in SQL?

Rank inside the group with ROW_NUMBER() or DENSE_RANK() OVER (PARTITION BY group ORDER BY metric DESC) in a CTE or subquery, then filter the rank in the outer query. A window function cannot be used directly in WHERE.