Ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST.
Pick the right ranking function based on tie behavior.
SELECT employee_id,
department_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rn
FROM employees;`ROW_NUMBER` always assigns distinct numbers, even when sort values tie.
SELECT employee_id,
department_id,
salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees;When two rows tie for rank 1, the next rank becomes 3.
SELECT employee_id,
department_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dense_salary_rank
FROM employees;Use `DENSE_RANK` when you want 1, 1, 2 rather than 1, 1, 3.
SELECT customer_id,
lifetime_value,
NTILE(4) OVER (ORDER BY lifetime_value DESC) AS quartile
FROM customers;`NTILE` is handy for quick segmentation such as quartiles or deciles.
SELECT player_id,
score,
PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM leaderboard;Great for percentile-style reporting without manual math.
SELECT player_id,
score,
CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM leaderboard;`CUME_DIST` answers what share of rows are less than or equal to this one.
Canonical patterns used constantly in apps and analytics.
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;This is one of the most useful window-function patterns for production SQL.
WITH ranked AS (
SELECT product_id,
category_id,
revenue,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY revenue DESC
) AS rn
FROM product_metrics
)
SELECT *
FROM ranked
WHERE rn <= 3;Swap `ROW_NUMBER` for `RANK` or `DENSE_RANK` when ties should be handled differently.
WITH ranked AS (
SELECT employee_id,
department_id,
salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM employees
)
SELECT *
FROM ranked
WHERE rnk <= 3;This returns all rows tied within the top ranks, which can exceed 3 rows per group.