Predict the Output of a Complex SQL JOIN with Aggregation and Filtering
Analyze the given SQL query that involves multiple JOINs, aggregation, and HAVING clauses, and predict the final output.
Challenge prompt
Consider the following database schema with two tables: Employees(emp_id, emp_name, dept_id) Salaries(emp_id, salary) Given the SQL query below, predict the exact output it will produce: SELECT d.dept_id, COUNT(e.emp_id) AS num_employees, AVG(s.salary) AS avg_salary FROM Employees e JOIN Salaries s ON e.emp_id = s.emp_id JOIN (SELECT DISTINCT dept_id FROM Employees) d ON e.dept_id = d.dept_id GROUP BY d.dept_id HAVING AVG(s.salary) > 60000 ORDER BY avg_salary DESC; Assuming the following data: Employees: emp_id | emp_name | dept_id 1 | Alice | 101 2 | Bob | 102 3 | Charlie | 101 4 | David | 103 5 | Eve | 101 Salaries: emp_id | salary 1 | 70000 2 | 50000 3 | 65000 4 | 55000 5 | 72000 What will be the output rows generated by this query?
Guidance
- • Carefully follow each JOIN condition and understand which rows from each table get associated.
- • Calculate the count of employees and the average salary per department before applying the HAVING filter.
- • Remember that HAVING filters groups after aggregation and ORDER BY sorts the final output.
Hints
- • First, join Employees with Salaries to match salaries to employees.
- • Then identify unique department IDs and join them to ensure all related records are grouped.
- • For each department, compute the average salary and count, then filter groups having avg_salary > 60000.
Starter code
SELECT d.dept_id, COUNT(e.emp_id) AS num_employees, AVG(s.salary) AS avg_salary
FROM Employees e
JOIN Salaries s ON e.emp_id = s.emp_id
JOIN (SELECT DISTINCT dept_id FROM Employees) d ON e.dept_id = d.dept_id
GROUP BY d.dept_id
HAVING AVG(s.salary) > 60000
ORDER BY avg_salary DESC;Expected output
dept_id | num_employees | avg_salary 101 | 3 | 69000
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.