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

Q&A DQ Courses
, ,
#1

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.

WITH
    customer_by_country AS(
        SELECT country,
               first_name || ' ' || last_name customer_name,
               customer_id
        FROM customer
        ),
           
total_purchased_by_customer AS(
     SELECT cbc.country,
            cbc.customer_name,
            SUM(inv.total) 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
Output shows 'ties' for best customer in each country
#2

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

WITH 
    countries AS
            (SELECT
             c.customer_id customer_id,
             c.first_name || " " || c.last_name full_names,
             c.country
             FROM customer c
             ORDER BY 3),
             
     purchases AS 
            (SELECT
             c.country country,
             c.full_names full_names,
             CAST(SUM(i.total) 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 
        p.country country,
        p.full_names customer_name,
        MAX(p.total_purchased) total_purchased
     FROM purchases p
     GROUP BY 1;
1 Like