Solution proposal
I have come out with a complex, but effective solution.
First I get the latest year when an employee was hired. it turns out to be 2009.
I consider one employee is currently hired if his/her latest t_salaries.to_date year is lower or equal to aggregated year. Some employees have left the company, so a cummulative sum wouldn't make sense.
Using CTE's I created a series of year from 1990 to 2009. Then another CTE with the hire and leave year for every employee.
For the studied year, an employeed is counted if his/her leave year is <= studied year. Every year, only those employees currently employeed will be counted.
Finally, join different tables and CTE's to get three columns" year, gender and no_employees.
Ask me if you want to check my query.
Hi Daniel!
Thanks for reaching out and sharing your solution with the Community!
Your solution seems to be following a correct logic. Of course, feel free to share it if you like.
In terms of the code - we all know there are oftentimes several potential solutions to a certain SQL programming problem. Since we are teaching, we are being very careful to not teach all SQL tools at once.
The solution you provide includes the use of common table expressions - a tool we teach in our Advanced SQL Course (https://learn.365datascience.com/courses/preview/advanced-sql/) Please feel free to check it out (and complete it if you haven't done this already!). In this course, we mainly want to focus on the interaction between SQL and Tableau.
Hope this helps.
Kind regards,
Martin
Submit an answer
