%%sql WITH country_count AS (SELECT country, (CASE WHEN COUNT(country) = 1 THEN 'Other' ELSE country END) as country_or_other, COUNT(customer_id) as total_customers_per_country FROM customer GROUP BY country ), country_sales AS (SELECT c.country, SUM(i.total) as total_sales, COUNT(i.invoice_id) as count_of_sales FROM customer as c LEFT JOIN invoice as i ON i.customer_id = c.customer_id GROUP BY c.country ), sort_table AS (SELECT country_or_other as country_name_for_sort, (CASE WHEN country_or_other == 'Other' THEN 1 ELSE 0 END) AS sort FROM country_count ) SELECT cc.country_or_other as country_name, SUM(cc.total_customers_per_country) as total_customers, ROUND(SUM(DISTINCT cs.total_sales), 2) as total_sales, ROUND((SUM(cs.total_sales)) / (SUM(cc.total_customers_per_country)), 2) as avg_sales_per_customer, ROUND((SUM(cs.total_sales)) / (SUM(cs.count_of_sales)), 2) as avg_order_value FROM country_count as cc LEFT JOIN country_sales as cs ON cs.country = cc.country LEFT JOIN sort_table as st ON st.country_name_for_sort = cc.country_or_other GROUP BY country_name ORDER BY st.sort, total_sales DESC
What I expected to happen:
What actually happened:
And just for visual help I also ran the country_count table on its own:
|Czech Republic||Czech Republic||2|
|United Kingdom||United Kingdom||3|
I have checked the solution on this problem but my code is quite different than the code provided. Everything is working except for the summed up customer count for the Other countries group. I have figured out that it is counting the Other countries as 15, 15 times giving 15 * 15 = 225. It was also doing that to the total_sales column until I added DISTINCT. However, I can’t do that for the country name or it only counts the Other country one time giving me 1.
My first attempt at this problem was so far from right I just deleted everything and started over to come up with the code above. I have now spent multiple hours on this one problem and I am not sure how I can fix this last number.
Can you give me some direction? Will the code above work with a small change or do I need to start over again?
Thanks for your help!