Salary against department total
mediumSalary against department total
Microsoft SQL Interview Question
Microsoft's finance team wants to compare each person's salary with the total salary bill of their department, side by side.
For every employee, show their name, department and salary, plus the total salary of their department as department_total. Keep one row for each employee.
Asked of
- Data Analyst
- BI Analyst
- Analytics Engineer
- Data Engineer
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 | department_total |
|---|---|---|---|
| Diego Alvarez | Analytics | 168000 | 302000 |
| Hannah Weber | Analytics | 134000 | 302000 |
| James Park | Engineering | 128000 | 455000 |
| Mei Lin | Engineering | 185000 | 455000 |
| Ravi Kumar | Executive | 240000 | 240000 |
| Sofia Rossi | Engineering | 142000 | 455000 |
Explanation
Mei Lin, Sofia Rossi and James Park work in Engineering, and their salaries add up to 455,000. That total appears on each of their three rows. Ravi Kumar is the only person in Executive, so that department total equals Ravi Kumar's own salary.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Microsoft
Difficulty
medium
Topic
window functions
Your query
Starting the SQL engine...