Employee salary increase with promotion
Question
You are given the following table containing historical employee salaries for company XYZ:
Table: EmployeeSalaries
| employee_ID | salary | promoted | year |
|---|---|---|---|
| 1 | 80000 | y | 2020 |
| 1 | 70000 | n | 2019 |
| 1 | 65000 | n | 2018 |
| 2 | 65000 | n | 2020 |
| 2 | 65000 | y | 2019 |
| 2 | 60000 | n | 2018 |
| 3 | 61000 | n | 2019 |
| 3 | 60000 | y | 2018 |
| 3 | 55000 | n | 2018 |
Given the above table, write a SQL query to return the average percent that an employee's salary increased from the prior year when they are promoted.
For example, employee_ID 1 had a promotion in 2020 and their pay increased from 70,000 in 2019 to 80,000 in 2020, or a 14.2% increase from their prior salary. You will need to do this calculation across every record where promoted = 'y' and return the average of the results.
You can view/query this table in an interactive SQL fiddle here.