How can you use a subquery in the FROM clause combined with window functions to find the top 3 highest paid employees per department?

hard📝 Application Q9 of 15

SQL - Subqueries

ASELECT * FROM employees WHERE salary IN (SELECT TOP 3 salary FROM employees);

BSELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees) AS ranked WHERE rn <= 3;

CSELECT * FROM employees ORDER BY salary DESC LIMIT 3;

DSELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

Step-by-Step Solution

Solution:

Step 1: Understand the use of window functions
ROW_NUMBER() assigns a rank to employees within each department ordered by salary descending.
Step 2: Use subquery in FROM clause
The subquery creates a derived table with ranks, then the outer query filters to top 3 per department.
Step 3: Check other options
Options A, C, and D do not correctly find top 3 per department.
Final Answer:
Use subquery with ROW_NUMBER() and filter ranks <= 3 -> Option B
Quick Check:
Window functions + derived tables = top N per group [OK]

Quick Trick: Use ROW_NUMBER() in subquery to rank per group [OK]

Common Mistakes:

MISTAKES

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes