SQL

Rank Records within Groups using Window Functions

Learn to use SQL window functions like ROW_NUMBER() and RANK() to assign ranks to records within specific groups, perfect for leaderboards or top-N queries.

SELECT
    product_id,
    category_id,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) as rn_in_category,
    RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) as rank_in_category
FROM
    sales_data
ORDER BY
    category_id, rn_in_category;

How it works: This query uses `ROW_NUMBER()` and `RANK()` window functions to assign a rank to each product based on `sales_amount` within its `category_id`. `ROW_NUMBER()` gives a unique rank even for ties, while `RANK()` assigns the same rank to tied values. `PARTITION BY` defines the groups for ranking, making it ideal for leaderboards or 'top N per category' analyses.

Rank Records within Groups using Window Functions

Need help integrating this into your project?