Find Top N Items Per Group Using Window Functions

WITH RankedSales AS (
    SELECT
        s.region,
        s.salesperson_name,
        s.total_sales,
        ROW_NUMBER() OVER (PARTITION BY s.region ORDER BY s.total_sales DESC) as rn
    FROM
        sales s
)
SELECT
    region,
    salesperson_name,
    total_sales
FROM
    RankedSales
WHERE
    rn <= 3; -- Get top 3 salespeople per region

How it works: This query uses a Common Table Expression (CTE) and the `ROW_NUMBER()` window function to find the top N items (e.g., top 3 salespeople) within each group (e.g., `region`). `PARTITION BY region` divides the data into separate groups, and `ORDER BY total_sales DESC` assigns a rank within each group based on sales. Finally, the outer query filters to retrieve only those rows with a rank less than or equal to N. This is highly useful for analytical reporting.