Dev Nest Guide
sqlintermediate10 minutes

Predict the Result of a Multi-Table Join with GROUP BY and HAVING

Analyze the given SQL query with multiple joins, aggregation, and filtering to determine the final output.

Challenge prompt

Given two tables, Employees and Sales, predict the output of the following SQL query. The Employees table contains employee_id and department, and the Sales table contains sale_id, employee_id, and sale_amount. The query joins the tables, aggregates total sales per department for employees with sales above 500, and filters departments with total sales above 1000.

Guidance

  • • Understand how the INNER JOIN pairs records between Employees and Sales based on employee_id.
  • • Focus on grouping results by department and how aggregation functions like SUM work.
  • • Note how the HAVING clause filters groups after aggregation.

Hints

  • • Remember that HAVING filters aggregated groups, while WHERE filters rows before aggregation.
  • • Check which employees have sales greater than 500 and only those contribute to total department sales.

Starter code

CREATE TABLE Employees (employee_id INT, department VARCHAR(20));
INSERT INTO Employees VALUES
(1, 'Sales'), (2, 'Marketing'), (3, 'Sales'), (4, 'Support');

CREATE TABLE Sales (sale_id INT, employee_id INT, sale_amount INT);
INSERT INTO Sales VALUES
(101, 1, 700), (102, 1, 200), (103, 2, 300), (104, 3, 900), (105, 4, 400);

SELECT e.department, SUM(s.sale_amount) AS total_sales
FROM Employees e
JOIN Sales s ON e.employee_id = s.employee_id
WHERE s.sale_amount > 500
GROUP BY e.department
HAVING SUM(s.sale_amount) > 1000;

Expected output

department | total_sales Sales | 1600

Core concepts

JOINsGROUP BYHAVING clauseAggregation functions

Challenge a Friend

Send this duel to someone else and see if they can solve it.