%%sql WITH information AS ( SELECT c.customer_id, COUNT(i.invoice_id) as purchase, c.country, SUM(i.total) as total FROM customer as c INNER JOIN invoice as i ON i.customer_id = c.customer_id GROUP BY c.customer_id ), country_classifier AS ( SELECT country, SUM(total) as country_total, SUM(purchase) as country_purchase, COUNT(customer_id) as num_of_customers, CASE WHEN COUNT(customer_id) = 1 THEN 'other' ELSE country END AS new_country FROM information GROUP BY country ORDER BY country_total DESC ) SELECT country, customer, total_sales, average_value, average_order FROM ( SELECT new_country as country, SUM(num_of_customers) as customer, SUM(country_total) as total_sales, SUM(country_total) / SUM(num_of_customers) as average_value, SUM(country_total) / SUM(country_purchase) as average_order, CASE WHEN new_country = "other" THEN 1 ELSE 0 END AS sort FROM country_classifier GROUP BY new_country ORDER BY sort ASC, total_sales DESC )
The output I got is attached below. When I compared it with the solution provided, I see that there is a suttle difference with respect to the total_sales caculated. As a result, this is affecting the calculations of other columns as well.
I was not able to understand the first part of the code provided in the solution. Also I noticed that unit_price from invoice_line table is used to calculate the total sales. I have not really understood this logic. There is a column called total in the invoice table. Is this not sufficient to calculate the required details? I was able to find the answer by joining only the customer and invoice tables, while the solution provided is joining an additional table invoice_line.
It would really of great help if someone can identify where I am wrong wrt to my code. That would help me to correct my understanding and the logic in developing my code.
Thank you for your support.