Found a better solution?Am i wrong?Building and Organizing Complex Queries_8/10 (challenge)

Screen Link:
https://app.dataquest.io/m/373/building-and-organizing-complex-queries/8/challenge-each-countrys-best-customer

My Code:

WITH
    cust AS
        (
        SELECT * FROM customer
        ),
    total_cus AS
        (
            SELECT SUM(i.total) total_purchases, c.customer_id FROM customer c
            LEFT JOIN invoice i 
            ON c.customer_id = i.customer_id
            GROUP BY first_name)
    SELECT country, first_name || " "|| last_name customer_name, 
           MAX(total_purchases) 
           FROM cust
           LEFT JOIN total_cus 
           ON cust.customer_id = total_cus.customer_id
           GROUP BY country
           ORDER BY 1;

What I expected to happen: I did the challenge but i wanted to experiment before following the indications to see if i think correctly. The goal was to find the best customer fo each country. I started with the previous exercice because the missions are always well structured and i generalized the code. The fact is the answer i found is almost identical to the answer expected.Except that i actually found people who spent more but are not found through the solution code provided and through the tables. Maybe i’m the one mistaking. What do you think? Please help me…

What actually happened: Australia and USA are the one where there is a mistake. Don’t ask me why. I like SQL but not that much. The first two pictures are what is expected. The two following are my results.

and

I didn’t read your question and code in full, so there may be other issues. But here’s what I found that for sure is wrong.

When creating total_cus, you’re grouping by the first name. What if two customers share the same first name?

Regarding, for example, the USA result. Looking at the customer table, I found out that Frank Ralston’s id is 24. Looking at the invoice table for this customer id, we get the following:

invoice_id customer_id invoice_date billing_address billing_city billing_state billing_country billing_postal_code total
18 24 2017-02-02 00:00:00 162 E Superior Street Chicago IL USA 60611 4.95
161 24 2018-01-13 00:00:00 162 E Superior Street Chicago IL USA 60611 7.92
200 24 2018-03-08 00:00:00 162 E Superior Street Chicago IL USA 60611 7.92
395 24 2019-07-28 00:00:00 162 E Superior Street Chicago IL USA 60611 9.9
417 24 2019-09-01 00:00:00 162 E Superior Street Chicago IL USA 60611 9.9
511 24 2020-05-01 00:00:00 162 E Superior Street Chicago IL USA 60611 6.93
529 24 2020-06-12 00:00:00 162 E Superior Street Chicago IL USA 60611 13.86
580 24 2020-10-25 00:00:00 162 E Superior Street Chicago IL USA 60611 9.9

The total column doesn’t add up to the 145.53 you got.

Thanks for the answer. I’ll look into it. I thought the left join was the problem but the result is the same with an inner join. The really strange thing is that just two clients are concerned. If i find why i got this, i’ll try to explain it here. Thanks again

I hinted at the “why” (at least partially): it’s because you’re grouping by the first name, but multiple customers can have the same name.

Of course! I understand now. You’ve been a huge help. I added the last name and got the same result. Now everything makes sense. And with the columns i’ve chose, i get why the inner join didn’t correct my grouping mistake. I’ll pay more attention and I understand even more the need to have well defined subqueries. Thanks a lot! :ok_hand:

1 Like