BLACK FRIDAY EXTRA SAVINGS EVENT - EXTENDED
START FREE

With regards to question 190-6, should it be rephrased for clarity?

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.

1 Like

I found this screen a little confusing too. However, I think your suggestions might be a little confusing as well…or maybe it’s just me?

Suggestion #1:

Since employees have multiple customers but customers don’t have multiple employees assigned to them, the answer to your suggestion here would always be = 1, regardless of the conditions you place on the customer. Also, the use of OR here is confusing because that implies we should be using UNION when in fact we should be using INTERSECT.

Suggestion #2:

This suggestion is confusing to me because every employee is actually located in Canada and employees do not spend money in the store…that’s what customers do! :sunglasses: Perhaps what you meant was that these two agents are the only agents with customers who live in the USA that spent over $90. This is true. Therefore, any agents other than these two will have a big fat 0 next to their name in the final result because we are asked to find the total number (a COUNT) of customers who fit a particular criteria for each sales agent. So, for all the other agents, the number of customers that they have is 0.

Suggestion #3:

Because INNER JOIN will only return rows that appear in both tables, we will lose rows from employee for agents who do not have any customers that meet our criteria. As mentioned above, that’s not what we want. The question is really asking for summary statistics on the employees who have customers with particular properties and NOT about the customers themselves. I guess this is what DQ meant by learning to “think in SQL!”

I think it just takes some time to wrap your head around the question which is actually very clear:

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.

Since there are three Sales Support Agents, our final result should have three rows, even if that sales agent doesn’t have customers in the USA who spend over $90…it just means that the answer to “how many customers” will be zero for that agent.

It finally made sense to me when I put parenthesis around the customer condition part of the question and just read it without that text:

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.

So, in the end, I believe this screen shouldn’t be rephrased for clarity but rather re-read for clarity. :stuck_out_tongue_winking_eye:

I know this is quite a late response to your original post but since SQL is a popular topic for generating questions, others mind find this discussion helpful…or at least I can hope it is!