Query regarding Q2

Question

The solution provided does return any contracts that are signed after 4 years of hiredate and before 5 years. This is due to condition "year(s.from_date) - year(hire_date) >=5" in the inner join.

eg - For emp_no = 10501, hiredate is "1987-04-20". This person has a salary of 60090 effective from "1991-09-08", which is 4 years after their hire date and hence should be returned by the query. However this data is not returned by the provided solution.

My solution to this problem:

SELECT e.emp_no,DENSE_RANK() OVER w as employee_salary_ranking,s.salary ,e.hire_date ,s.from_date
, year(s.from_date) - year(hire_date) AS years_from_start , month(s.from_date) - month(hire_date) As months_from_start
FROM employees e
INNER JOIN salaries s on e.emp_no = s.emp_no and s.from_date > date_add(hire_date, interval 4 year)
WHERE e.emp_no between 10500 and 10600
WINDOW w as (PARTITION BY e.emp_no ORDER BY s.salary DESC)
;

Am i on the correct track?

Also, i would like to include months_from_start in the query. Currently i am using " month(s.from_date) - month(hire_date)" but it is not returning me the required data. I want this column to return 52 months for the above example.

Thanks

Answer 1

Hi Tanika!
Thanks for reaching out.

Your approach to solving the problem seems correct in principle, with the use of date_add() to find salaries effective after a certain period following the hire_date. You are on the right track with using an interval of 4 years to join the tables. However, there's a slight issue with calculating the months_from_start as you've discovered.

The expression month(s.from_date) - month(hire_date) only calculates the difference between the month numbers without considering the year difference, which is why it's not providing the correct data for months. To include the number of months from the start date, you can use the TIMESTAMPDIFF function, which correctly calculates the full month difference between two dates.

Here is how you can modify your query to calculate the years_from_start and months_from_start correctly:

SELECT e.emp_no,
DENSE_RANK() OVER w as employee_salary_ranking,
s.salary,
e.hire_date,
s.from_date,
TIMESTAMPDIFF(YEAR, e.hire_date, s.from_date) AS years_from_start,
TIMESTAMPDIFF(MONTH, e.hire_date, s.from_date) AS total_months_from_start
FROM employees e
INNER JOIN salaries s on e.emp_no = s.emp_no
AND s.from_date BETWEEN DATE_ADD(e.hire_date, INTERVAL 4 YEAR)
AND DATE_ADD(e.hire_date, INTERVAL 5 YEAR)
WHERE e.emp_no between 10500 and 10600
WINDOW w as (PARTITION BY e.emp_no ORDER BY s.salary DESC);

Hope this helps.
Best,
Tsvetelin

Query regarding Q2

Submit an answer

related questions