Screen Link: Learn data science with Python and R projects
Here´s a short story of how I came up with the solution. First I wrote a solution that gave the right output. Then I checked the DQ answer and found it way more complicated than my first solution. So, I went to the community to check what other learners thought about the DQ solution and such. Looking through the topics I realized that I made quite a common mistake of leaving one of the non-aggregate columns from the
SELECT clause outside the
GROUP BY clause. So, I came back to the DQ app to try to find another way around but still somewhat simpler than the one offered by the DQ .
So, here´s my solution:
WITH customer_purchases AS ( SELECT i.customer_id, c.country, SUM(i.total) total_purchases FROM invoice i INNER JOIN customer c ON c.customer_id = i.customer_id GROUP BY i.customer_id, c.country ), max_purchase AS ( SELECT country, MAX(total_purchases) country_max_purchase FROM customer_purchases GROUP BY country ) SELECT mp.country country, c.first_name || " " || c.last_name customer_name, cp.total_purchases total_purchased FROM customer c INNER JOIN customer_purchases cp ON c.customer_id = cp.customer_id INNER JOIN max_purchase mp ON mp.country = cp.country AND mp.country_max_purchase = cp.total_purchases ORDER BY country;
What do you think about it? Am I missing some possible pitfalls?