Screen Link:
My Code:
WITH customer_country_purchases AS
(SELECT first_name || ' ' || last_name AS customer_name,
customer.customer_id,
country,
total
FROM customer
INNER JOIN invoice
ON customer.customer_id = invoice.customer_id),
customer_totals AS
(SELECT customer_name,
SUM(total) AS total_purchased,
customer_id,
country
FROM customer_country_purchases
GROUP BY customer_id),
country_max_purchase AS
(SELECT *,
MAX(cmp.total_purchased) AS total_purchased
FROM customer_totals AS cmp
INNER JOIN customer_country_purchases AS ccp
ON cmp.customer_id = ccp.customer_id
GROUP BY cmp.country
ORDER BY cmp.country)
SELECT country,
customer_name,
total_purchased
FROM country_max_purchase;
The solution was marked as correct but was quite different from the proposed answer so I am wondering if I have taken an unexpected short-cut somewhere?