SQL
Advanced Ranking and Analytics with SQL Window Functions
Explore SQL window functions like `ROW_NUMBER()` and `PARTITION BY` to perform advanced calculations such as ranking items within specific groups for analytical purposes.
SELECT
product_id,
category_id,
price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rank_in_category
FROM products
ORDER BY category_id, rank_in_category;
How it works: Window functions perform calculations across a set of table rows that are related to the current row. `PARTITION BY` divides the rows into logical groups (like `category_id` here), and `ORDER BY` within the `OVER` clause defines the order within each partition. `ROW_NUMBER()` then assigns a unique, sequential integer to each row within its partition based on that order, perfect for finding top-N items per group.