SQL
Ranking Rows within Groups using SQL Window Functions
Learn to apply SQL window functions like ROW_NUMBER() or RANK() to assign ranks to rows within partitions, useful for leaderboards or top N queries per category.
SELECT
product_id,
category,
sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank_within_category
FROM
product_sales
ORDER BY
category, rank_within_category;
How it works: This SQL snippet demonstrates how to use the ROW_NUMBER() window function to assign a unique rank to each row within a specified group (partition). The PARTITION BY clause divides the rows into groups based on the 'category' column, and then ORDER BY sales DESC sorts the rows within each category by sales in descending order. This is incredibly useful for analytical queries, such as finding the top-selling product in each category or creating leaderboards.