SQL
Ranking Data with SQL Window Functions
Utilize SQL window functions like `ROW_NUMBER()` or `RANK()` to assign ranks or sequential numbers within partitions of your dataset, crucial for leaderboards or top N queries.
SELECT
product_name,
category,
sales_amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) as rank_in_category,
RANK() OVER (ORDER BY sales_amount DESC) as overall_rank
FROM
product_sales
ORDER BY
category, rank_in_category;
How it works: This query leverages SQL window functions to calculate ranks based on sales data. `ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC)` assigns a unique sequential rank to each product within its respective category, ordered by sales amount in descending order. `RANK() OVER (ORDER BY sales_amount DESC)` calculates an overall rank for all products based purely on their sales amount. Window functions are invaluable for analytical tasks like creating leaderboards or identifying top performers within groups without complex self-joins.