SQL
Calculate Ranks within Groups using SQL Window Functions
Learn to assign ranks to records within defined groups (e.g., users within categories or products within departments) using SQL window functions like RANK() or DENSE_RANK() for advanced analytics.
SELECT
product_id,
category_id,
sales_amount,
RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) as rank_within_category,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) as dense_rank_within_category,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) as row_num_within_category
FROM
products_sales;
How it works: This SQL snippet demonstrates how to use window functions to calculate ranks within specific groups (partitions). `PARTITION BY category_id` divides the data into separate groups for each category. `ORDER BY sales_amount DESC` then orders the records within each group by sales amount in descending order. `RANK()` assigns a unique rank, skipping numbers for ties. `DENSE_RANK()` assigns a rank without skipping numbers for ties. `ROW_NUMBER()` assigns a unique sequential number to each row within its partition.