Consecutive increases in annual earnings
Question
Suppose you are given two tables, showing the number of hours worked as well as the hourly rates for specific employees by year:
Table: employee_hours
employee_id | hours_worked | year |
---|---|---|
1 | 2200 | 2020 |
1 | 1900 | 2019 |
1 | 2300 | 2018 |
2 | 2200 | 2020 |
2 | 2000 | 2019 |
2 | 1850 | 2018 |
3 | 1850 | 2019 |
3 | 1920 | 2018 |
Table: employee_hourly_rates
employee_id | hourly_rate_usd | year |
---|---|---|
0001 | 45 | 2020 |
0001 | 42 | 2019 |
0001 | 40 | 2018 |
0002 | 40 | 2020 |
0002 | 40 | 2019 |
0002 | 40 | 2018 |
0003 | 55 | 2019 |
0003 | 50 | 2018 |
Given the above tables, write a SQL query to return the employees who generated more income (hours * hourly rate) in at least two consecutive years (either 2018-2019, 2019-2020, or all three years from 2018-2020).
You can view/query these tables in an interactive SQL fiddle here.