I attempted with challenge question (Each Country’s Best Customer) and here’s my code.
WITH customer_info AS ( SELECT c.first_name || " " || c.last_name customer_name, c.country, SUM(i.total) total_purchased FROM customer c INNER JOIN invoice i ON c.customer_id = i.customer_id GROUP BY c.customer_id ), country_max AS ( SELECT country, MAX(total_purchased) max_purchased FROM customer_info GROUP BY country ), country_best_customer AS ( SELECT cm.country, ( SELECT ci.customer_name FROM customer_info ci WHERE ci.country = cm.country AND cm.max_purchased = ci.total_purchased ) customer_name, cm.max_purchased total_purchased FROM country_max cm ) SELECT * FROM country_best_customer
With this code, I get a number of missing customers in the customer_name column.
However, I figure out that if I switch the positions of
customer c and
invoice in my
customer_info subquery (i.e.
FROM invoice i INNER JOIN customer c), this somehow gets me the full table without missing customers (the correct final answer).
( SELECT c.first_name || " " || c.last_name customer_name, c.country, SUM(i.total) total_purchased FROM invoice i INNER JOIN customer c ON c.customer_id = i.customer_id GROUP BY c.customer_id )
I’m glad that I managed to get the final answer but I don’t understand why switching the position makes a difference since it is an inner join. Does anyone have any idea why this is happening? Thanks in advanced!