Rank Data within Partitions Using SQL Window Functions

SELECT
    product_id,
    category_id,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS row_num_in_category,
    RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rank_in_category,
    DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS dense_rank_in_category
FROM
    sales
ORDER BY
    category_id, sales_amount DESC;

How it works: This SQL snippet showcases window functions to rank sales data. `ROW_NUMBER()` assigns a unique sequential integer to each row within its partition (defined by `category_id`), ordered by `sales_amount`. `RANK()` assigns the same rank to rows with identical values in the ordering column, leaving gaps. `DENSE_RANK()` also assigns the same rank to ties but does not leave gaps, providing consecutive ranks. This is highly useful for "top N" analyses within groups.