07 May 2024

Posted on:

06 Jan 2024

0

# Resolved:Would this be correct?

# What is the average annual salary paid to employees who started after the 1st of January 1997?
# to calaculate avg salary with the salary fluctuations
# the salary is multiplied by the fraction of the year that it was effective
# if to_date is 9999-01-01 then it is still active, the date is set to the current date
SELECT SUM(s.salary * DATEDIFF(s.to_date, s.from_date)) / SUM(DATEDIFF(s.to_date, s.from_date)) AS weighted_avg_salary
# the join is to find those who started after the 1st of January 1997
FROM employees.employees e JOIN employees.salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date >= '1997-01-01';

Instructor
Posted on:

17 Feb 2024

0

Hi Bruce!

Thanks for reaching out.

Thank you for sharing your detailed understanding of the question.

To be frank, we wanted to keep things simple when teaching the AVG() function. So, perhaps we should improve the text by saying we assume that after that date no salary changes have been applied for any of the employees in the company.

Alternatively, to apply a solution corresponding to the questions you specify, one can use window functions. That's what we do in our Advanced SQL course (https://learn.365datascience.com/courses/preview/advanced-sql/).

Here's a structure of a query which could help you solve the problem (substituting employee numbers with the years since 1997 until now):

`SELECT `
`    de2.emp_no, d.dept_name, s2.salary, AVG(s2.salary) OVER w AS average_salary_per_department`
`FROM`
`    (SELECT `
`    de.emp_no, de.dept_no, de.from_date, de.to_date`
`FROM`
`    dept_emp de`
`        JOIN`
`    (SELECT `
`        emp_no, MAX(from_date) AS from_date`
`    FROM`
`        dept_emp`
`    GROUP BY emp_no) de1 ON de1.emp_no = de.emp_no`
`WHERE`
`    de.to_date > SYSDATE()`
`        AND de.from_date = de1.from_date) de2`
`        JOIN`
`    (SELECT `
`    s1.emp_no, s.salary, s.from_date, s.to_date`
`FROM`
`    salaries s`
`        JOIN`
`    (SELECT `
`        emp_no, MAX(from_date) AS from_date`
`    FROM`
`        salaries `
`    GROUP BY emp_no) s1 ON s.emp_no = s1.emp_no`
`WHERE`
`    s.to_date > SYSDATE()`
`        AND s.from_date = s1.from_date) s2 ON s2.emp_no = de2.emp_no`
` JOIN`
`    departments d ON d.dept_no = de2.dept_no`
`GROUP BY de2.emp_no, d.dept_name`
`WINDOW w AS (PARTITION BY de2.dept_no)`
`ORDER BY de2.emp_no;`

Hope this helps.
Best,
Martin

Posted on:

07 May 2024

0

this is so complex!

Instructor
Posted on:

07 May 2024

0

Hi Moffat!

Thanks for reaching out.

At the beginning, it might seem complex but I am sure that as you advance with our courses you will be able to identify the structure and get used to writing queries of such length and complexity. Please ensure to not skip any lectures, exercises, and exams; we'll be here to help if necessary.

Hope this helps.
Kind regards,
Martin