SQL

Rank Items within Groups Using Window Functions

Learn how to use SQL window functions like ROW_NUMBER() or RANK() to assign ranks to rows within partitioned groups, perfect for leaderboards or top N lists per category.

SELECT
    product_id,
    category_id,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rank_in_category
FROM
    sales
WHERE
    sales_date >= '2023-01-01'
ORDER BY
    category_id, rank_in_category;

How it works: This query uses the `ROW_NUMBER()` window function to assign a unique rank to each product within its respective `category_id`, ordered by `sales_amount` in descending order. The `PARTITION BY category_id` clause ensures the ranking restarts for each new category. This is ideal for scenarios like finding the top-selling products per category, providing precise ordering without self-joins.

Rank Items within Groups Using Window Functions

Need help integrating this into your project?