Department headcount and pay
mediumDepartment headcount and pay
Salesforce SQL Interview Question
Salesforce's HR team is preparing next year's budget and needs a summary of the size and pay of each department.
For each department, find the number of employees as headcount and the average salary as avg_salary. Do not round the average.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
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
| department | headcount | avg_salary |
|---|---|---|
| Analytics | 2 | 151000 |
| Engineering | 3 | 151666.6667 |
| Executive | 1 | 240000 |
Explanation
Engineering has three employees in the example, earning 185,000, 142,000 and 128,000, so its average is 151,666.666... with the decimals kept. Executive has only Ravi Kumar, so its average is that one salary of 240,000.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Salesforce
Difficulty
medium
Topic
aggregations
Your query
Starting the SQL engine...