HR: Average Salary by Department

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The compensation committee is reviewing salary equity across departments. Calculate the average salary for each department.

Tables

Table: Employees

| emp_id | name | department | salary | hire_date |
| --- | --- | --- | --- | --- |
| 1 | Ashna | HR | 60000 | 2022-01-15 |
| 2 | Pinki | IT | 70000 | 2021-06-20 |
| 3 | Shruti | Finance | 65000 | 2020-09-10 |
| 4 | Parvati | Marketing | 62000 | 2023-03-05 |

Table: Performance

| perf_id | emp_id | rating | review_date |
| --- | --- | --- | --- |
| 101 | 1 | 4 | 2023-06-30 |
| 102 | 2 | 5 | 2023-06-30 |
| 103 | 3 | 3 | 2023-06-30 |
| 104 | 4 | 4 | 2023-06-30 |
| 105 | 1 | 5 | 2024-06-30 |
| 106 | 2 | 4 | 2024-06-30 |
| 107 | 3 | 4 | 2024-06-30 |
| 108 | 4 | 5 | 2024-06-30 |

Expected Output

| department | avg_salary |
| --- | --- |
| IT | 70000.0000 |
| Finance | 65000.0000 |
| Marketing | 62000.0000 |
| HR | 60000.0000 |

• Return: department, avg_salary (rounded to 2 decimal places)
• Sort by avg_salary descending