# Challenge: Each Country's Best Customer. Why the solution so complex when it can be easier?

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_purchased
FROM customer c
LEFT JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY c.customer_id
)

SELECT
country,
customer_name,
ROUND(MAX(total_purchased),2) total_purchased
FROM customer_country_purchases
GROUP BY country
``````

What I expected to happen:
My code gives me a table that lists each country’s best customers which is identical to the final result shown in the instructions. I have 2 problem for this mission:

1. Why the solution uses 3 WITH AS clause (code below) when 1 (my code above) is enough to solve this problem?
2. In the solution code (code below), for the subquery in the country_best_customer

(SELECT ccp.customer_id
FROM customer_country_purchases ccp
WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases)

why we can directly use cmp.country without even joining the ccp to cmp first? I thought the subquery runs independently thus we need a join to perform such function.

What actually happened:

``````Please see above
``````

Solution Code:

``````WITH
customer_country_purchases AS
(
SELECT
i.customer_id,
c.country,
SUM(i.total) total_purchases
FROM invoice i
INNER JOIN customer c ON i.customer_id = c.customer_id
GROUP BY 1, 2
),
country_max_purchase AS
(
SELECT
country,
MAX(total_purchases) max_purchase
FROM customer_country_purchases
GROUP BY 1
),
country_best_customer AS
(
SELECT
cmp.country,
cmp.max_purchase,
(
SELECT ccp.customer_id
FROM customer_country_purchases ccp
WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases
) customer_id
FROM country_max_purchase cmp
)
SELECT
cbc.country country,
c.first_name || " " || c.last_name customer_name,
cbc.max_purchase total_purchased
FROM customer c
INNER JOIN country_best_customer cbc ON cbc.customer_id = c.customer_id
ORDER BY 1 ASC
``````

Hello,

regarding your first question, I would point you to this thread explaining why some solutions for the SQL missions seem to be longer than they could be.

Granted, it is a good question, as I don’t personally believe it was sufficiently covered in the course. @Bruno was kind enough to explain it for us new learners.