ROW_NUMBER, RANK and DENSE_RANK

The three

ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC)
RANK()       OVER (PARTITION BY region ORDER BY total DESC)
DENSE_RANK() OVER (PARTITION BY region ORDER BY total DESC)

On values 100, 90, 90, 80:

ROW_NUMBER never ties and breaks them arbitrarily. RANK ties then skips. DENSE_RANK ties without skipping.

This comes up in most SQL interviews at analyst level.

Top N per group

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

ROW_NUMBER is usually right here because it guarantees exactly three rows. RANK would return four if two orders tied for third — which is sometimes what you want, so state your assumption.

Deduplication

The standard way to keep one row per key:

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM customers
) t WHERE rn = 1;

Keeps the most recently updated record per email and drops the rest. Add a tiebreaker to the ORDER BY if updated_at can be equal, otherwise which row survives is arbitrary.

NTILE

NTILE(4) OVER (ORDER BY total)   -- quartiles

Distributes rows into n buckets as evenly as it can.