Top-2 Highest-Paid Employees Under Each VP
Preview mode. Log in to edit, run, submit, and save progress.
Top-2 Highest-Paid Employees Under Each VP
You are given an Employees table with a management hierarchy. The CEO is the single root (manager_id IS NULL). Every direct report of the CEO is a VP. Write a SQL query that, for each VP, finds the top 2 highest-paid employees anywhere in their subtree (direct and indirect reports, excluding the VP themselves). In case of salary ties, use RANK (so there may be more than 2 rows for a VP if two employees share the 2nd-highest salary). Return (vp_id, employee_id, salary, rnk) ordered by vp_id, then rnk, then employee_id. Table: Employees
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| name | TEXT | Employee name |
| manager_id | INT | NULL for the CEO |
| salary | INT | Employee salary |
Examples
Recursively collect all descendants for each VP. Exclude the VP themselves. RANK salaries DESC per VP. Return only rnk <= 2.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.