The PARTITION BY clause in SQL is often used with window functions to perform calculations across sets of rows that are related to the current row.
RANK(): This column shows the rank of each sale within each salesperson partition, skipping ranks for ties.
DENSE_RANK(): This column shows the dense rank of each sale within each salesperson partition, without skipping ranks for ties.
SUM(): This column shows the total sales for each salesperson.
AVG(): This column shows the average sale amount for each salesperson.
To find the top sale for each salesperson based on the sale_amount, we can use the ROW_NUMBER() window function partitioned by salesperson_id:
SELECT
sale_id,
salesperson_id,
sale_amount,
sale_date,
ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_amount DESC) as rank
FROM
sales;
Filtering to Get Top Sales
To get only the top sale for each salesperson, you can wrap the above query in a subquery and filter on the rank:
SELECT
sale_id,
salesperson_id,
sale_amount,
sale_date
FROM
(SELECT
sale_id,
salesperson_id,
sale_amount,
sale_date,
ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_amount DESC) as rank
FROM
sales) ranked_sales
WHERE
rank = 1;
| sale_id | salesperson_id | sale_amount | sale_date |
|---|
| 1 | 101 | 500 | 2024-01-01 |
| 4 | 102 | 700 | 2024-01-01 |
| 6 | 103 | 400 | 2024-01-01 |
We can use Common Table Expression (CTE) to rewrite the last query as follows:
WITH RankedSales AS (
SELECT
sale_id,
salesperson_id,
sale_amount,
sale_date,
ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_amount DESC) AS rank
FROM
sales
)
SELECT
sale_id,
salesperson_id,
sale_amount,
sale_date
FROM
RankedSales
WHERE
rank = 1;