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!

SQL Tableau Problem 3 - SQL Code

Submit an answer