Level 3 - Question 9: Window Functions and Analytics
Scenario
Your sales team wants to see each salesperson's performance ranked within their region, along with running totals of sales.
Task
Write a query using window functions (RANK, ROW_NUMBER, SUM OVER) to display salesperson name, region, sales amount, their rank within the region, and a running total of sales by region.
Sample Schema: sales
salesperson
region
sales_amount
John
East
5000
Mary
East
7000
Steve
West
6000
Anna
West
8000
Tom
East
4500
Solution:
SELECT
salesperson,
region,
sales_amount,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS region_rank,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS row_num,
SUM(sales_amount) OVER (PARTITION BY region ORDER BY sales_amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales
ORDER BY region, sales_amount DESC;
This query demonstrates powerful analytical functions:
RANK(): Assigns ranking with gaps for ties
ROW_NUMBER(): Assigns unique sequential numbers
SUM() OVER: Calculates running totals within each partition (region)
PARTITION BY: Divides data into groups for separate calculations
Window functions are essential for advanced reporting and analytics without requiring self-joins or subqueries.