Challenge 8: Each Country's Best Customer

Hello, I am doing this Challenge 8:Each Country’s Best Customer. I have written an SQL query as mentioned below. But I am unable to understand what is missing in my code. Any help is greatly appreciated

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

My Code:

WITH customer_country_purchases AS (SELECT c.customer_id,
c.first_name||" "||c.last_name customer_name,
c.country country,
SUM(i.total) total_pur 
from customer c 
INNER JOIN invoice i on c.customer_id=i.customer_id
GROUP BY 1,2)

SELECT country,customer_name,ROUND(max(total_pur),2) total_purchased
from customer_country_purchases
GROUP BY country

What I expected to happen:

country customer_name total_purchased
Argentina Diego Gutiérrez 39.60
Australia Mark Taylor 81.18
Austria Astrid Gruber 69.30
Belgium Daan Peeters 60.39
Brazil Luís Gonçalves 108.90
Canada François Tremblay 99.99
Chile Luis Rojas 97.02
Czech Republic František Wichterlová 144.54
Denmark Kara Nielsen 37.62
Finland Terhi Hämäläinen 79.20
France Wyatt Girard 99.99
Germany Fynn Zimmermann 94.05
Hungary Ladislav Kovács 78.21
India Manoj Pareek 111.87
Ireland Hugh O’Reilly 114.84
Italy Lucas Mancini 50.49
Netherlands Johannes Van der Berg 65.34
Norway Bjørn Hansen 72.27
Poland Stanisław Wójcik 76.23
Portugal João Fernandes 102.96
Spain Enrique Muñoz 98.01
Sweden Joakim Johansson 75.24
USA Jack Smith 98.01
United Kingdom Phil Hughes 98.01

What actually happened:

|country|customer_name|total_purchased|

Argentina,Diego Gutiérrez,39.6
Australia,Mark Taylor,81.18
Austria,Astrid Gruber,69.3
Belgium,Daan Peeters,60.39
Brazil,Luís Gonçalves,108.9
Canada,François Tremblay,99.99
Chile,Luis Rojas,97.02
Czech RepublicFrantišek Wichterlová,144.54
Denmark,Kara Nielsen,37.62
Finland,Terhi Hämäläinen,79.2
FranceWyatt Girard99.99
GermanyFynn Zimmermann94.05
HungaryLadislav Kovács78.21
IndiaManoj Pareek111.87
IrelandHugh O’Reilly114.84
ItalyLucas Mancini50.49
NetherlandsJohannes Van der Berg65.34
NorwayBjørn Hansen72.27
PolandStanisław Wójcik76.23
PortugalJoão Fernandes102.96
SpainEnrique Muñoz98.01
SwedenJoakim Johansson75.24
USAJack Smith98.01
United KingdomPhil Hughes98.01

Hello @aditya1257. I finished this exercise and I got a similar answer as you. I ran your code and the output is correct. However, the ROUND(max(total_pur), 2) is not needed. The example rounds the total_purchased column to 2 decimal values. However, the instructions say that rounding is not needed. If you just change the last column to ‘max(total_pur) total_purchased’, then the answer is correct.

Hope this helps.

1 Like

Thank you so much @samson.john