ROW_NUMBER, RANK, DENSE_RANK, NTILE

Number or rank rows within a partition by an ordering.

Syntax

row_number() OVER (PARTITION BY g ORDER BY x DESC)
rank()       OVER (...)    -- 1, 2, 2, 4
dense_rank() OVER (...)    -- 1, 2, 2, 3
ntile(n)     OVER (...)    -- bucket 1..n
percent_rank() / cume_dist() OVER (...)

row_number() gives 1, 2, 3… with no ties; tied rows get an arbitrary order unless the ORDER BY is total. Use it for "exactly one row per group" (latest record, de-duplication).
rank() gives ties the same number and leaves gaps (1, 2, 2, 4). dense_rank() leaves no gaps (1, 2, 2, 3). Use them for "top N including ties".
ntile(n) splits the ordered rows into n nearly equal buckets (quartiles, deciles).
percent_rank() = (rank − 1) / (rows − 1); cume_dist() = share of rows ≤ current.
Top N per group: compute the rank in a subquery/CTE, then WHERE rn <= N outside.
NULL: NULLs sort last in ascending order (first in DESC), so they rank last/first accordingly — use NULLS LAST when ranking DESC.

Examples

Compare the three starter

SELECT product_name, price,
       row_number() OVER (ORDER BY price DESC) AS row_num,
       rank()       OVER (ORDER BY price DESC) AS rnk,
       dense_rank() OVER (ORDER BY price DESC) AS dense_rnk
FROM products
ORDER BY price DESC, product_name;

Where prices tie, rank and dense_rank repeat while row_number keeps counting.

Top 3 products by revenue per category shop

WITH revenue AS (
  SELECT p.category_id, p.product_name, sum(oi.quantity * oi.unit_price - oi.discount_amount) AS revenue
  FROM order_items oi
  JOIN products p USING (product_id)
  GROUP BY p.category_id, p.product_id, p.product_name
), ranked AS (
  SELECT *, row_number() OVER (PARTITION BY category_id ORDER BY revenue DESC, product_name) AS rn
  FROM revenue
)
SELECT c.category_name, r.product_name, round(r.revenue, 2) AS revenue, r.rn
FROM ranked r
JOIN categories c USING (category_id)
WHERE r.rn <= 3
ORDER BY c.category_name, r.rn;

The ranking is computed in a CTE so the outer query can filter on it.

Quartiles of customers by spend starter

SELECT customer_id, sum(quantity) AS units,
       ntile(4) OVER (ORDER BY sum(quantity) DESC, customer_id) AS quartile
FROM orders
GROUP BY customer_id
ORDER BY quartile, units DESC;

Quartile 1 holds the biggest buyers.

Pitfalls

Using row_number for 'top 3 including ties' (it breaks ties arbitrarily).
Ranking without a deterministic tie-breaker, so results differ between runs.
Filtering WHERE row_number() ... = 1 in the same query level.
Forgetting PARTITION BY and ranking across the whole table.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

BigQuery: QUALIFY row_number() OVER (...) <= 3 avoids the wrapper query.
Snowflake: QUALIFY row_number() OVER (...) <= 3 avoids the wrapper query.