Last answered:

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

3 answers ( 1 marked as helpful)
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

Submit an answer