With regards to question 190-6, should it be rephrased for clarity?
Here is original ask:
“Write a query that works out how many customers that are in the USA and have purchased more than $90 are assigned to each sales support agent.”
Would it be better to ask:
“Write a query that works out how many employees (sales support agents) are assigned to the customers in the USA OR for customers with spending over $90. For each of the employees calculate a number of customers who are BOTH are in the USA AND have spending over $90”
The original questions conflates the two. If you look at the intersect there are only 2 employees/sales agents assigned that are BOTH in the USA and spend over $90 (Margaret Park and Steve Johnson). So technically speaking customers in the USA with spend over $90 do NOT have Jane Peacock assigned.
Alternatively the answer should NOT include the LEFT JOIN in tip and correct the answer. To me (not sure what others think) the original ask questions is about the customer in the US with spend over $90 hence there should be INNER JOIN on customer that satisfies restrict conditions (probably would also be better query if we assume large database set).
Does that make sense?
Click here to open the screen in a new tab.