SQL
SQL Query: Rank Items Within Groups Using Window Functions
Learn to rank items, such as products by sales within each category, using SQL window functions like ROW_NUMBER() for detailed reporting.
SELECT
item_id,
category,
sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank_within_category
FROM
products_sales
ORDER BY
category, rank_within_category;
How it works: This SQL snippet demonstrates how to assign a rank to rows within defined groups using a window function. `ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC)` partitions the data by `category` and then assigns a sequential rank based on `sales` in descending order within each category. This is highly useful for scenarios like finding the top N products per category, leaderboards, or analyzing performance within specific segments.