This is my code that I came up with on my own which arrives at the correct answer:
WITH sum_by_cust AS ( SELECT c.country AS country, c.first_name||" "||c.last_name as customer_name, SUM(i.total) AS customer_total_purchased FROM customer as c INNER JOIN invoice as i ON i.customer_id = c.customer_id GROUP BY c.country, customer_name ORDER BY customer_total_purchased DESC ) SELECT country, customer_name, MAX(customer_total_purchased) as total_purchased FROM sum_by_cust GROUP BY country;
This is the DQ Answer which is much different:
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
When I came up with my answer after trial and error. I mapped it out on paper and tried to visualize it simple steps using the knowledge that I have so far. I would not have thought of what the DQ answer did.
Is my answer bad or not acceptable in some way? What would people expect in practice? I feel like I’m missing something.
Thank you for your time