Paid above the department average
hardPaid above the department average
Google SQL Interview Question
Google's people analytics team is reviewing pay and wants to see who earns more than the typical salary in their own department.
List the name, department and salary of every employee who earns more than the average salary of their own department.
Asked of
- Data Analyst
- Data Scientist
- 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 |
|---|---|---|
| Diego Alvarez | Analytics | 168000 |
| Mei Lin | Engineering | 185000 |
Explanation
Engineering's three salaries average about 151,667, and only Mei Lin, at 185,000, is above that. In Analytics, Diego Alvarez earns more than the department average of 151,000. Ravi Kumar is the only person in Executive, so that salary equals the department average and is not above it.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Google
Difficulty
hard
Topic
subqueries
Your query
Starting the SQL engine...