I don't think that the solution given to problem 3 is accurate. Firstly, from a pedantic point of view, the task is to calculate average salaries which can be filtered by department. The solution provided calculates the average salaries per department in SQL, then averages the selected departments in Tableau. I don't think that this is ideal since the average of averages is not necessarily the same as calculating the overall average. The main issue I have with the solution is that the SQL code calculates the average salaries by using the employee number to join the employees, salaries and dept_emp tables as follows: SELECT e.gender, d.dept_name, ROUND(AVG(s.salary), 2) AS salary, YEAR(s.from_date) AS calendar_year FROM t_salaries s JOIN t_employees e ON s.emp_no = e.emp_no JOIN t_dept_emp de ON de.emp_no = e.emp_no JOIN t_departments d ON d.dept_no = de.dept_no GROUP BY d.dept_no , e.gender , calendar_year HAVING calendar_year <= 2002 ORDER BY d.dept_no; Since a single employee can have multiple entries in the dept_emp table (they can work in multiple departments in the course of their employment) and in the salaries table, there is a many-to-many relationship between these two tables. For example employee 10124 worked in department 4 from 1991 to 1999 and in department 6 from 1999 to 2002. The SQL code above will include their salary every year from 1991 to 2002 as being in department 4 and department 6 when calculating the average. I think that it is necessary to follow an approach more like in problem 2: select the employee numbers, salaries, department numbers, start dates and end dates from t_salaries s JOIN t_dept_emp de ON s.emp_no=de.emp_no, then calculate for each row whether the department date range and salary date range overlap (valid rows), then join the valid rows to the employees and department tables and calculate the average. The final thing that I noticed is that the properties of the sample dataset cause a lot of data to be excluded from the calculation anyway. There are about 300,000 distinct employee numbers in the dept_emp table, 100,000 in the salaries table and 135,000 in the employees table. However, there are only about 46,000 employees in the intersection between the employees and salaries tables, so at least half of the data in the salaries table is not being included in the averages. This does not particularly make a difference to the methodology, but you would hope that a real company would not be failing to pay so many of its staff while paying so many people who are not employees, so it may be a good idea to ensure that the same employees feature in the employees, salaries and dept_emp tables!

Hi Philip! Thanks for reaching out. Regarding your last paragraph, may I please note that this dataset uses data that is considered to be fabricated and has been modified to fit the purposes of this course to a satisfactory level. Therefore, the observations you are making are valid; it is just that we've accepted/assumed they are not going to be a significant issue while solving the given task. Please allow me to proceed with answering the rest of your questions in a couple of days. Thank you. Hope this helps.Best,Martin

back

Follow this topic

Philip Coell

Last answered:

May 03, 2020

Posted on:

April 28, 2020

SQL Tableau Problem 3 - SQL Code

1 answers ( 0 marked as helpful)

Martin Ganchev

Posted on:

May 03, 2020

Submit an answer

Content