SQL
Ranking Items Within Groups Using Window Functions
Learn to use SQL window functions like `ROW_NUMBER()` with `PARTITION BY` to rank items within distinct categories, useful for leaderboards or top N lists.
SELECT
product_category,
product_name,
price,
ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY price DESC) AS rank_in_category
FROM
products
ORDER BY
product_category, rank_in_category;
How it works: This query uses the `ROW_NUMBER()` window function to assign a rank to each product within its respective `product_category`. The `PARTITION BY product_category` clause ensures that the ranking restarts for each new category, and `ORDER BY price DESC` ranks products by price from highest to lowest within that category. This is highly useful for generating 'top N' lists or leaderboards for different segments of data directly in SQL.