Efficiently Rank Records within Groups Using Window Functions

SELECT
    product_category,
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY sales_amount DESC) AS rn,
    RANK() OVER (PARTITION BY product_category ORDER BY sales_amount DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY product_category ORDER BY sales_amount DESC) AS drk
FROM
    sales_data
ORDER BY
    product_category, rn;

How it works: This query demonstrates how to use SQL window functions to rank sales data. `ROW_NUMBER()` assigns a unique, sequential integer to each row within its partition (product_category), 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 identical values but without gaps. These are invaluable for creating leaderboards or selecting top N items per group, significantly enhancing analytical capabilities.