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

Screen Link:

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.