[Solved] How can you modify this query to get the top 3 salespeople per region based on total sales?SELECT region, salesperson, SUM(amount) AS total_sales FROM sales GROUP BY region, salesperson; | SQL

SQL - Advanced Query Patterns

How can you modify this query to get the top 3 salespeople per region based on total sales?

SELECT region, salesperson, SUM(amount) AS total_sales FROM sales GROUP BY region, salesperson;

AAdd WHERE total_sales <= 3

BWrap it in a subquery and use ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC) to filter top 3

CUse HAVING COUNT(*) <= 3

DAdd ORDER BY total_sales DESC LIMIT 3

Step-by-Step Solution

Solution:

Step 1: Aggregate sales per salesperson per region
The original query groups sales per salesperson and region.
Step 2: Use ROW_NUMBER() in subquery to rank salespeople per region
Wrap the aggregation in a subquery, then assign row numbers partitioned by region ordered by total_sales descending.
Step 3: Filter top 3 per region
Filter rows where row number <= 3 to get top 3 salespeople per region.
Final Answer:
Wrap it in a subquery and use ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC) to filter top 3 -> Option B
Quick Check:
Use subquery with ROW_NUMBER() after aggregation [OK]

Quick Trick: Aggregate first, then rank with ROW_NUMBER() in subquery [OK]

Common Mistakes:

Filtering on aggregate in WHERE instead of HAVING or subquery
Using LIMIT without partitioning
Misusing HAVING with COUNT for top N

Master "Advanced Query Patterns" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

How can you modify this query to get the top 3 salespeople per region based on total sales?

Step 1: Aggregate sales per salesperson per region

Step 2: Use ROW_NUMBER() in subquery to rank salespeople per region

Step 3: Filter top 3 per region

Final Answer:

Quick Check:

Want More Practice?