Ranking Data within Groups Using ROW_NUMBER()

SELECT
    product_id,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rank_in_category
FROM
    products;

How it works: This query uses the `ROW_NUMBER()` window function to assign a sequential rank to each product within its respective `category_id`, ordered by `price` in descending order. The `PARTITION BY` clause divides the dataset into logical groups, and `ORDER BY` defines the ranking criteria within each group. This is extremely useful for generating leaderboards, finding top N items per group, or selecting unique rows based on specific criteria.