Salary rank within department
mediumSalary rank within department
Netflix SQL Interview Question
Netflix's compensation team wants to see where each person's salary sits within their own department.
For every employee, show their name, department and salary, plus their rank within their department as salary_rank, where 1 is the highest salary. Employees with equal salaries share the same rank, and the next rank is skipped.
Asked of
- Data Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
employeesTable16 rows
| Column Name | Type |
|---|---|
| employee_id | BIGINT |
| name | VARCHAR |
| department | VARCHAR |
| salary | BIGINT |
| manager_id | BIGINT |
| hire_date | DATE |
employeesExample Input
| employee_id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Ravi Kumar | Executive | 240000 | NULL | 2018-01-15 |
| 2 | Mei Lin | Engineering | 185000 | 1 | 2018-06-04 |
| 3 | Sofia Rossi | Engineering | 142000 | 2 | 2019-02-18 |
| 4 | James Park | Engineering | 128000 | 2 | 2019-09-02 |
| 7 | Diego Alvarez | Analytics | 168000 | 1 | 2018-10-22 |
| 8 | Hannah Weber | Analytics | 134000 | 7 | 2020-01-13 |
Example Output
| name | department | salary | salary_rank |
|---|---|---|---|
| Diego Alvarez | Analytics | 168000 | 1 |
| Hannah Weber | Analytics | 134000 | 2 |
| James Park | Engineering | 128000 | 3 |
| Mei Lin | Engineering | 185000 | 1 |
| Ravi Kumar | Executive | 240000 | 1 |
| Sofia Rossi | Engineering | 142000 | 2 |
Explanation
In Engineering, Mei Lin has the highest salary and ranks 1, followed by Sofia Rossi at 2 and James Park at 3. The ranking starts again in each department, so Diego Alvarez is also 1 in Analytics.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Netflix
Difficulty
medium
Topic
window functions
Your query
Starting the SQL engine...