Problem 3: Solution in SQL

Question

Hello everyone. I would like to ask if the code in the solution includes the salaries of people who had the same salary for 2 and more years. For example, if people with the salary they began to earn in 2001 and had still amount in 2002 were included in calculating average per year.
I think that the code does not consider it. (1) Also, I think that in the obtained dataset people, who are no longer employed in the company will not show up because of join with employees table for acquiring gender of employee. (2) Not sure how to resolve problem (2), but could my query address at least problem (1)?

SELECT
	y.calendar_year AS calendar_year,
	AVG(s.salary) AS avg_salary,
    e.gender AS gender,
    de.dept_no AS dept_no,
    d.dept_name as dept_name    
FROM
	(	SELECT DISTINCT(YEAR(from_date)) AS calendar_year
		FROM t_salaries
        UNION SELECT DISTINCT(YEAR(to_date)) AS calendar_year
        FROM t_salaries) as y
        JOIN
	t_salaries s ON YEAR(s.from_date) <= calendar_year AND calendar_year <= YEAR(s.to_date)
		JOIN
	t_dept_emp de ON s.emp_no = de.emp_no
		JOIN
	t_departments d ON de.dept_no = d.dept_no
		JOIN
	t_employees e ON s.emp_no = e.emp_no
WHERE
	calendar_year <= 2002
GROUP BY
	dept_name,
    calendar_year,
    gender
ORDER BY
	calendar_year ASC,
    dept_name ASC,
    gender DESC
;

Sorry for the long code, I know you have better things to do than read it :D, but it would really help my understanding. Thank you. Best, Maros Jevocin

Answer 1

Hi Maros! Thanks for reaching out. Thank you very much for sharing this query. You are right when you say that more clarity is needed in this case, so thank you for reaching out.

We step on the assumption that the data in the t_salaries table has been recorded for every year, practically. You can run the following query to see that in practice.

SELECT * FROM t_salaries LIMIT 100;

Hope this answers your question but please feel free to reply should you need more clarifications or still have any doubts. 2. According to the query we've used, the average will only be taken until the year when they last worked for the company. Please execute the following query to observe that there are many such employees in the company.

SELECT 
emp_no, MAX(to_date) AS max_date
FROM
t_salaries
GROUP BY emp_no
ORDER BY max_date
LIMIT 10000;

Hope this helps.
Best,
Martin

Problem 3: Solution in SQL

Submit an answer