Last answered:

22 May 2024

Posted on:

25 Feb 2023


Resolved: 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

        WHEN cal_year BETWEEN YEAR(hire_date) AND YEAR(end_date) THEN 1
        ELSE 0
    END) AS no_of_emp
        e.emp_no, e.gender, e.hire_date, MAX(d.to_date) AS end_date
        employees e
    JOIN dept_emp d ON e.emp_no = d.emp_no
    GROUP BY e.emp_no) a
        CROSS JOIN
        YEAR(hire_date) AS cal_year
        YEAR(hire_date) >= 1990) b
GROUP BY cal_year , gender
ORDER BY cal_year;
2 answers ( 1 marked as helpful)
Posted on:

13 Jan 2024


Thank you for raising this, I am confused as well.I hope 365datascience repond to this.

Posted on:

22 May 2024


Hi Eslam and Temitope!

Thanks for reaching out!

Please accept my apologies for the delayed response.

In your query, the condition WHERE YEAR(hire_date) >= 1990 is applied only in the subquery b, which generates distinct calendar years. However, this condition isn't applied to the main query, which performs a cross join between the generated calendar years and the result set from the subquery a. This means that for each calendar year in the result set, the query will attempt to calculate the number of employees for all years regardless of the condition.

In contrast, the solution query directly filters the data based on the condition HAVING calendar_year >= 1990, ensuring that only years from 1990 and onwards are included in the result set. This results in fewer rows returned compared to the first query, where data for all years may be included.

Hope this helps.

Submit an answer