Resolved: Would this be correct?

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

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