SQL
Rank Items Within Categories Using SQL Window Functions
Learn to assign ranks to items within distinct groups (e.g., products within categories by sales) using RANK() or DENSE_RANK() window functions in SQL.
SELECT
product_id,
category_id,
sales_amount,
RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS sales_rank,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS dense_sales_rank
FROM
products;
How it works: This query demonstrates how to rank products by their sales_amount within each category_id using SQL window functions. PARTITION BY category_id divides the dataset into independent groups, and ORDER BY sales_amount DESC determines the ranking order within each group. RANK() assigns ranks with gaps for ties (e.g., 1, 1, 3), while DENSE_RANK() assigns consecutive ranks without gaps for ties (e.g., 1, 1, 2). This is highly useful for "top N per group" scenarios, like identifying the top-selling products in each specific product category.