Another Prospective for the Solution
I am quite confused regarding the solution as the given solution is just checking the year when an employee starteed working
However, maybe there are some employees that are employed before 1990 but still working in the company by that year and even after
and this can be applied to all years
Also there is nothing mentioned regarding when the employee has left
I had a solution trying to consider those factors, it seems quite complicated and do not know if I am right or not
SELECT
cal_year,
gender,
SUM(CASE
WHEN cal_year BETWEEN YEAR(hire_date) AND YEAR(end_date) THEN 1
ELSE 0
END) AS no_of_emp
FROM
(SELECT
e.emp_no, e.gender, e.hire_date, MAX(d.to_date) AS end_date
FROM
employees e
JOIN dept_emp d ON e.emp_no = d.emp_no
GROUP BY e.emp_no) a
CROSS JOIN
(SELECT DISTINCT
YEAR(hire_date) AS cal_year
FROM
employees
WHERE
YEAR(hire_date) >= 1990) b
GROUP BY cal_year , gender
ORDER BY cal_year;
1 answers ( 0 marked as helpful)
Thank you for raising this, I am confused as well.I hope 365datascience repond to this.