Your solution to Building and Organizing Complex Queries page 6 is as follows:
WITH customers_usa_gt_90 AS ( SELECT * FROM customer_usa INTERSECT SELECT * FROM customer_gt_90_dollars ) SELECT e.first_name || " " || e.last_name employee_name, COUNT(c.customer_id) customers_usa_gt_90 FROM employee e LEFT JOIN customers_usa_gt_90 c ON c.support_rep_id = e.employee_id WHERE e.title = 'Sales Support Agent' GROUP BY 1 ORDER BY 1;
However this returns three rows instead of the correct 2. The required result is to list all employee’s with client amounts. Where the clients are from the USA AND spent more than 90 dollars. You query returns an employee with 0 clients in that segment. And thus is incorrect. What am I missing?
my solution returns two rows:
WITH customers_usa_gt_90 AS ( SELECT * FROM customer_usa INTERSECT SELECT * FROM customer_gt_90_dollars ) SELECT e.first_name || " " || e.last_name employee_name, COUNT(cu.customer_id) customers_usa_gt_90 FROM customers_usa_gt_90 cu LEFT JOIN employee e ON e.employee_id = cu.support_rep_id WHERE e.title = "Sales Support Agent" GROUP BY 1 ORDER BY 1;
The problem I think is that you utilize the LEFT JOIN the other way round. You should be joining employee to customers_usa_gt_90 maintaining all the values from customers_usa_gt_90 and ignoring values from employee where conditions where not met.
let me know if Im clowning or not ^^