Output shows 'ties' for best customer in each country

Q&A DQ Courses
,
#1

I am stuck on this challenge. My output is showing the correct names and max purchases, but it contains ‘ties’ for best customer in each country. At the start of the challenge it explicitly says there should be no ties. What am I doing wrong?

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

WITH customer_country_purchases AS
(SELECT c.customer_id customer_id, SUM(i.total) total_spent, c.country country
 FROM customer c
 INNER JOIN invoice i ON c.customer_id = i.customer_id
 GROUP BY c.customer_id, country),

country_max_purchase AS
(SELECT country, MAX(total_spent) total_purchased
FROM customer_country_purchases
 GROUP BY country)
 
 SELECT cmp.country country, c.first_name || ' ' || c.last_name customer_name, cmp.total_purchased total_purchased
  FROM country_max_purchase cmp
  INNER JOIN customer_country_purchases ccp ON cmp.total_purchased = ccp.total_spent
  INNER JOIN customer c on ccp.customer_id = c.customer_id
  ORDER BY country;

The output shows the correct names and max purchases but with multiple ties for best customer. For example Austria shows two customers Astrid Gruber and Alexandre Rocha with total purchases of 69.3

The output says ‘There are more rows than there should be’

Can someone tell me what I have done wrong? Any help much appreciated!

1 Like
#2

Hey @d.meaney19,

Try using group by clause in your final query, It’s just a very complex query and I can’t understand it. And when I did this mission I remember that the Dataquest solution is also very complex. Instead of that, Try using this query, I find this more simple.

WITH
   customer_country AS
     (
      SELECT customer_id,first_name||" "||last_name AS customer_name,country
      FROM customer
     ),
   total_purchased AS
            (
             SELECT customer_id,SUM(total) AS total 
             FROM invoice GROUP BY customer_id
            )
            
SELECT 
 cc.country AS country, 
 cc.customer_name AS customer_name, 
 MAX(tp.total) AS total_purchased 
FROM customer_country AS cc 
INNER JOIN total_purchased AS tp ON
cc.customer_id = tp.customer_id
GROUP BY country
ORDER BY country

Try to understand it, if you need any help, feel free to ask!!

Anyone, if there is any reason that we shouldn’t use it then, please comment down!!

1 Like
#3

Thanks for that! I tried adding group by to my final query but it’s still not showing the correct output. I’d just like to know what is actually wrong with my query - I can’t see why it wouldn’t give the right answer

#4

Hello @d.meaney19

This modification of your code passed the test.

There were several problems:

  1. Grouping customer_country_purchasing by both customer_id and country created a problem.
  2. Not including the key customer_id in country_max_purchase created a problem.
  3. Joining the final query with total_purchased created a problem.

I think the query was a bit hard to read. I created a topic on this challenge. Kindly check the link to see how I solved it.

Cheers!

WITH customer_country_purchases AS
(SELECT c.customer_id, SUM(i.total) total_spent, c.country country
 FROM customer c
 INNER JOIN invoice i ON c.customer_id = i.customer_id
 GROUP BY c.customer_id),
 

country_max_purchase AS
(SELECT country, customer_id, MAX(total_spent) total_purchased
FROM customer_country_purchases
 GROUP BY country)

 SELECT cmp.country country, c.first_name || ' ' || c.last_name customer_name, cmp.total_purchased total_purchased
  FROM country_max_purchase cmp
  INNER JOIN customer_country_purchases ccp ON cmp.customer_id = ccp.customer_id
  INNER JOIN customer c on ccp.customer_id = c.customer_id
  ORDER BY country;
1 Like
#5

Thankyou for your detailed reply!

Elsewhere in the forum, Bruno has said:

"you’re selecting three columns, one has an aggregate function, the other two don’t, but you’re only grouping by one of the remaining ones.

Some database systems won’t accept this as valid code, it will just yield an error. And some will do something that isn’t what you expect. Details can be read in the first link in my reply above."

So thats why I felt I had to group by country and customer id. But you helped me see what I was doing wrong, so cheers!

2 Likes