23 Dec 2020

Posted on:

21 Dec 2020

0

# Problem 3: Solution in SQL

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_noWHERE	calendar_year <= 2002GROUP BY	dept_name,    calendar_year,    genderORDER 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
Posted on:

23 Dec 2020

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.
1. 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_dateFROMt_salariesGROUP BY emp_noORDER BY max_dateLIMIT 10000;`
Hope this helps.
Best,
Martin