SQL
SQL Window Functions for Ranking Data within Groups
Learn to use SQL window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() to assign ranks to rows within partitions, useful for leaderboards or top N queries.
SELECT
product_id,
product_name,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_by_price_in_category,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_dense_by_price_in_category
FROM
products
ORDER BY
category, rank_by_price_in_category;
How it works: This SQL snippet demonstrates the use of window functions to rank data within specific groups. `ROW_NUMBER()` assigns a unique sequential integer to rows within each partition (defined by `PARTITION BY category`), ordered by price. `RANK()` and `DENSE_RANK()` also provide ranks, handling ties differently (DENSE_RANK gives consecutive ranks, RANK skips ranks after ties). This is highly useful for scenarios like finding the top N items per category or creating leaderboards.