SQL
Rank Rows Using SQL Window Functions
Use SQL window functions like `ROW_NUMBER()` or `RANK()` to assign a unique rank or sequence number to rows within a partition, useful for leaderboards or top N queries.
SELECT
product_id,
product_name,
category,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products
ORDER BY category, rank_in_category;
How it works: This query calculates a rank for each product based on its price within its respective category. `RANK() OVER (PARTITION BY category ORDER BY price DESC)` assigns a rank, where products with the same price within the same category will receive the same rank. `PARTITION BY` divides the data into groups (categories), and `ORDER BY` within the `OVER` clause defines the ranking criteria within each group. This is useful for identifying top items within specific groups without aggregating them.