SQL
Rank Rows within Groups (e.g., Top N per category)
Learn to rank records within specific groups, such as finding the top 3 products per category, using SQL window functions for advanced data analysis.
SELECT product_name, category, price, rank_within_category FROM (
SELECT
product_name,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_within_category
FROM products
) AS ranked_products
WHERE rank_within_category <= 3;
How it works: This query uses `ROW_NUMBER()` as a window function to assign a rank to each product within its respective category, ordered by price. A subquery is then used to filter for the top N (e.g., 3) products in each category, enabling advanced analytical reporting for use cases like 'top selling items per department'.