Given the table sales with columns region, salesperson, and amount, what will this query return?

medium📝 query result Q13 of 15

SQL - Advanced Query Patterns

Given the table sales with columns region, salesperson, and amount, what will this query return?

SELECT region, salesperson, amount FROM (SELECT region, salesperson, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales) AS ranked WHERE rn = 1;

AAll sales records sorted by amount descending.

BAll sales records where amount equals 1.

CThe highest sale amount record for each region.

DThe lowest sale amount record for each region.

Step-by-Step Solution

Solution:

Step 1: Analyze ROW_NUMBER() with PARTITION BY region
Rows are grouped by region and ranked by amount descending, so rn=1 is highest amount per region.
Step 2: Filter WHERE rn = 1
This keeps only the top-ranked row per region, i.e., the highest sale amount record.
Final Answer:
The highest sale amount record for each region. -> Option C
Quick Check:
ROW_NUMBER() = 1 per group returns top record [OK]

Quick Trick: Filter WHERE rn=1 to get top record per group [OK]

Common Mistakes:

Thinking it returns all rows sorted
Confusing rn=1 with lowest amount
Assuming amount = 1 filters rows

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

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More SQL Quizzes

Given the table sales with columns region, salesperson, and amount, what will this query return?

Step 1: Analyze ROW_NUMBER() with PARTITION BY region

Step 2: Filter WHERE rn = 1

Final Answer:

Quick Check:

Want More Practice?