Hi all! I got stuck on sales by country for a while. It takes me a long time to understand the solution that DQ provided, unfortunaly I still can’t understand by now. If someone can help with explaining it, I appreciate it.
Luckily, I think I answered the question in my way, but not sure that why someone used unit_price to calculate the total_sales, I just used the total from the invoice table and it worked.
I think my code is easy to understand. I hope it helps people who have the same problem.
%%sql with co as (select country,(case when count(customer_id)=1 then 'other' else country end) as country_other, count(customer_id) as count_customer from customer group by 1), al as (select co.country_other, round(sum(i.total),2) as total_sales,count(i.invoice_id) as total_purchase from co inner join invoice i on co.country=i.billing_country group by 1), t3 as (select country_other,sum(count_customer) as total_customer from co group by 1) select al.country_other,t3.total_customer, al.total_sales, al.total_purchase, round((al.total_sales/t3.total_customer),2) as avg_sales_per_customer, round((al.total_sales/al.total_purchase),2) as avg_order, (case when al.country_other='other' then 1 else 0 end) as sort_col from al inner join t3 on al.country_other=t3.country_other group by 1,2 order by 7 asc,3 desc