SQL
Ranking Data within Partitions using Window Functions
Assign sequential ranks to rows within defined groups or partitions based on specific criteria using SQL window functions like ROW_NUMBER(), DENSE_RANK(), or RANK().
SELECT
product_id,
category_id,
price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rank_in_category_by_price,
RANK() OVER (PARTITION BY category_id ORDER BY price DESC) as rank_with_ties,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) as dense_rank_with_ties
FROM
products
ORDER BY
category_id, rank_in_category_by_price;
How it works: This snippet showcases SQL window functions for ranking. It calculates three types of ranks for products within each category, ordered by price in descending order. `ROW_NUMBER()` assigns a unique sequential integer to each row within its partition, `RANK()` assigns the same rank to ties and skips subsequent numbers, while `DENSE_RANK()` assigns the same rank to ties but does not skip any numbers in the sequence. This is invaluable for 'top N per group' queries.