WITH alias AS (SELECT customer_id, SUM(total) total FROM invoice i GROUP BY 1) SELECT c.country country, c.first_name || ' ' || c.last_name customer_name, MAX(a.total) total_purchased FROM customer c INNER JOIN alias a ON c.customer_id = a.customer_id GROUP BY c.country;
I came up with the above code for the challenge and it gave the correct output. The answer code is more complex, though.
My first question: What did I miss here? Why is my code giving the correct output?
Secondly, in the answer code, I couldn’t understand the purpose of the following subquery and its code.
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 )