Hello, I wrote my solution for the screen:
WITH purchases_country AS ( SELECT i.customer_id customer_id, c.country 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 ), country_max_purchase AS ( SELECT country, customer_id, MAX(total_purchases) max_purchase FROM purchases_country GROUP by country ) SELECT cmp.country country, c.first_name || " " || c.last_name customer_name, round(cmp.max_purchase, 2) total_purchased FROM customer c INNER JOIN country_max_purchase cmp ON cmp.customer_id = c.customer_id ORDER BY country ;
The result I got is similar to the given one. For answer system “round” function must be removed.
I’m not claiming that my solution is the best, it’s just my own work and I’m so happy that I was able to write it, complex queries are still hard for me.