Rank, dense_rank, and ntile

You know ROW_NUMBER, which always gives unique numbers. But what happens when rows have tied values? RANK and DENSE_RANK handle ties differently, and NTILE lets you divide rows into equal buckets.

rank_dense_rank.sql
sql
-- RANK vs DENSE_RANK on category revenue (ties!)
WITH category_rev AS (
  SELECT
    p.product_category_name,
    SUM(oi.price) AS revenue
  FROM order_items oi
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY p.product_category_name
)
SELECT
  product_category_name,
  revenue,
  RANK() OVER (ORDER BY revenue DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank
FROM category_rev
LIMIT 15;

-- RANK: ties get same number, next skips (1,2,2,4)
-- DENSE_RANK: ties get same number, no skip (1,2,2,3)

RANK skips numbers after ties. DENSE_RANK does not. Choose based on whether you want gaps.

GROUP BY collapses rows: 1000 rows become 27 (one per state). PARTITION BY keeps all 1000 rows but creates windows for calculation. Use GROUP BY when you want summary rows. Use PARTITION BY when you want to add a calculation to every row without losing detail.

Fill in the blanks: Complete the window function

Loading practice…

Matching exercise: Match the window function

Loading practice…

Code playground: Most expensive product per category

Loading practice…

Quiz: Quiz

Loading practice…

Hints: Hints

Loading practice…