Sales by country-Share my code

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.

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/6/albums-vs-individual-tracks

My Code:

%%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

@candiceliu93 I re-categorized your topic. Remember to use the correct category in future. Thanks.