Top-2 Highest-Paid Employees Under Each VP

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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

Database Schema (Inferred)

Employees

Column Name	Example Value
id	1
name	CEO
manager_id	NULL
salary	20000

Example

Employees

id	name	manager_id	salary
1	CEO	NULL	20000
2	VP1	1	15000
3	VP2	1	14000
4	Mgr1	2	9000
5	Mgr2	2	8500
6	Mgr3	3	8000
7	Emp1	4	5000
8	Emp2	4	4800
9	Emp3	5	5200
10	Emp4	6	6000
11	Emp5	6	3000

Output

vp_id	employee_id	salary	rnk
2	4	9000	1
2	5	8500	2
3	6	8000	1
3	10	6000	2

Explanation:

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.