CYBER WEEK - EXTRA SAVINGS EVENT
TRY A FREE LESSON

I think I either spotted a mistake in your query or an opportunity for me to learn!

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 ^^

2 Likes

Hello @reinder666, welcome to the community!

I think you misunderstood what the question is as this is not incorrect. Here’s the instruction:

  1. 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.

Please notice that it asks for a query that returns how many clients living in the USA that have spent over $90 are assigned to each sales support agent. It never said you should hide support agents with 0 clients with these prerequisites.

If the are three sales support agents, all of them should be listed, no matter how many clients are assigned to each of them.

I hope this helps you.

One last thing, when creating a topic, make sure to paste a link for the mission you refer to. That helps a lot the one answering the question.

3 Likes

I started to have that stingy feeling in the back of my head I might of misinterpreted the question. Thanks! Also how do i post a link to the question? Is it just the url? Thanks a lot!

2 Likes

Yes, just paste the url.

Also, when the your problem is solved, it is nice to mark the answer as solution. By doing this, you help others with similar questions to find the answer.

I had the same confusion as @reinder666. @otavios.s, thanks for your explanations, it does help, but I still think the instruction is put together in an unnecessarily ambiguous way. For me

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.

reads with an emphasis on the customers and not sales support agents. The fact there’s been so many views and questions on this matter is only a proof for that, in my opinion.

Kind regards :slight_smile:

I kindly disagree. The way I see, it is pretty clear. But if you have suggestions, feel free to contact Dataquest using the Contact Us button in the top of this page.

1 Like

Just spent a bit of time struggling with this exact same problem.

I feel that the instructions could be better phrased.

For Example

Write a query that works out for each sales agent the number of customers that they have that are in the USA AND who have also have purchased more than $90.

Hello,

I suggest you to contact Dataquest using the Contact Us button in the top of this page and let them know your thoughts.

1 Like

I was under the impression that Dataquest would monitor these types of community threads and act accordingly. Seems like a bit of a missed opportunity.

Anyways I’ve put my thoughts forward here. Obviously this is not really a big deal so I shall leave it at that.

All the best. :slight_smile: