Would this be correct?

Question

# 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';

Answer 1

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

Would this be correct?

Submit an answer

related questions