[Solved] You have a sales table:Salesperson | SalesAmountJohn | 500Jane | 700Joe | 700Jill | 600You want to assign ranks so that tied salespeople share the same rank... Which query correctly achieves this? | SQL

SQL - Window Functions Fundamentals

You have a sales table:

Salesperson | SalesAmount
John        | 500
Jane        | 700
Joe         | 700
Jill        | 600

You want to assign ranks so that tied salespeople share the same rank and the next rank number is not skipped. Which query correctly achieves this?

ASELECT Salesperson, SalesAmount, RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

BSELECT Salesperson, SalesAmount, RANK() OVER (PARTITION BY SalesAmount ORDER BY SalesAmount DESC) AS Rank FROM sales;

CSELECT Salesperson, SalesAmount, ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

DSELECT Salesperson, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

Step-by-Step Solution

Solution:

Step 1: Understand ranking requirements
Tied salespeople should share the same rank, and the next rank number should not skip any numbers.
Step 2: Choose correct function
DENSE_RANK() assigns same rank to ties and does not skip ranks. RANK() skips ranks after ties. ROW_NUMBER() never assigns same rank to ties. SELECT Salesperson, SalesAmount, RANK() OVER (PARTITION BY SalesAmount ORDER BY SalesAmount DESC) AS Rank FROM sales; misuses PARTITION BY.
Final Answer:
SELECT Salesperson, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales; -> Option D
Quick Check:
DENSE_RANK() for no skipped ranks after ties [OK]

Quick Trick: Use DENSE_RANK() to avoid skipping ranks after ties [OK]

Common Mistakes:

Master "Window Functions Fundamentals" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

You have a sales table: