SQL
Rank Items Within Groups Using SQL Window Functions
Efficiently rank rows within specific groups (e.g., products within categories) based on criteria like sales or quantity using SQL's powerful `ROW_NUMBER()` window function.
SELECT
product_id,
product_name,
category,
sales_amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rank_in_category
FROM products_sales
ORDER BY category, rank_in_category;
How it works: This SQL snippet uses the `ROW_NUMBER()` window function to assign a unique rank to each product within its respective `category`. The `PARTITION BY category` clause divides the result set into separate groups for each category, and `ORDER BY sales_amount DESC` specifies that products should be ranked by their `sales_amount` in descending order within each group. This is useful for identifying top performers or for pagination within grouped results.