Building and Organizing Complex Queries Challenge: Each Country's Best Customer

I solved this challenge differently. I searched through the community and I found one good solution. The solutions are different. So I decided to share.

    customer_by_country AS(
        SELECT country,
               first_name || ' ' || last_name customer_name,
        FROM customer
total_purchased_by_customer AS(
            SUM( total_purchased

    FROM customer_by_country cbc
    INNER JOIN invoice inv ON inv.customer_id = cbc.customer_id
    GROUP BY inv.customer_id)
SELECT * FROM total_purchased_by_customer
    GROUP BY 1
    HAVING total_purchased = MAX(total_purchased)
    ORDER BY 1; 
1 Like

I also solved it differently on the Data Analyst in Python Track.

    countries AS
             c.customer_id customer_id,
             c.first_name || " " || c.last_name full_names,
             FROM customer c
             ORDER BY 3),
     purchases AS 
             c.full_names full_names,
             CAST(SUM( AS Float) total_purchased
             FROM countries c
             LEFT JOIN invoice i ON i.customer_id = c.customer_id
             GROUP BY i.customer_id
             ORDER BY 1)
SELECT country,
        p.full_names customer_name,
        MAX(p.total_purchased) total_purchased
     FROM purchases p
     GROUP BY 1;
1 Like