Managers with Large Salaries
Microsoft
Problem Statement
Microsoft HR wants to ensure that compensation structures reflect leadership hierarchies. Specifically, they want to identify managers who are the highest earners within their own immediate team.
Write an SQL query to find the names of managers whose salary is strictly higher than the salary of all their direct reports.
Rules:
- A manager is any employee whose employee_id appears in the manager_id column of another employee.
- Only consider "direct reports" (employees who report directly to that manager).
- If a manager has no direct reports, they should not be included in the results.
- Output columns: manager_name.
- Results must be sorted by manager_name in ascending order.
Tests your understanding of
Self Join, Subqueries and Aggregation
Input Tables
Employees
| employee_id(INTEGER) | employee_name(VARCHAR) | salary(INTEGER) | manager_id(INTEGER) |
|---|---|---|---|
| 1 | Satya | 500000 | null |
| 2 | Amy | 300000 | 1 |
| 3 | Kevin | 250000 | 1 |
| 4 | Brad | 400000 | 2 |
| 5 | Judson | 100000 | 2 |
| 6 | Chris | 450000 | 3 |
Departments
| dept_id(INTEGER) | dept_name(VARCHAR) |
|---|---|
| 10 | Engineering |
| 20 | Finance |
| 30 | Sales |
| 40 | Marketing |
| 50 | HR |
| 60 | Legal |
Performance_Reviews
| employee_id(INTEGER) | rating(INTEGER) |
|---|---|
| 1 | 5 |
| 2 | 4 |
| 3 | 3 |
| 4 | 5 |
| 5 | 2 |
| 6 | 4 |
Expected Output
| manager_name(VARCHAR) |
|---|
| Satya |
Tags
MediumSelf JoinSubqueriesAggregation
15-20 min
62%