From the code I wrote below, I do not get the same results as the answer key. but I really do not understand why. !!!
Guided Project: Answering Business Questions Using SQL
5. Analyzing Sales by Country
%%sql
WITH customer_country AS
(
SELECT
c.country,
SUM(il.unit_price) total_sales,
COUNT(i.customer_id) as total_nr_of_customers,
CAST(il.unit_price AS FLOAT) / COUNT(distinct i.customer_id) customer_lifetime_value,
SUM(il.unit_price) / count(distinct i.invoice_id) average_order,
il.*
FROM invoice_line il
INNER JOIN invoice i ON i.invoice_id = il.invoice_line_id
INNER JOIN customer c ON c.customer_id = i.customer_id
group by 1
)
SELECT
CASE
WHEN (SELECT count(*) FROM customer where country = c.country) = 1
THEN “Other”
ELSE c.country
END AS country_ ,
cc.total_nr_of_customers customers,
cc.total_sales,
cc.customer_lifetime_value,
cc.average_order
FROM customer_country cc
INNER JOIN invoice i ON i.invoice_id = cc.invoice_line_id
INNER JOIN customer c ON c.customer_id = i.customer_id
GROUP BY country_
ORDER BY country_ ASC, total_sales DESC;
Hi! @ruken.imsik
Welcome to the community!
First, let’s talk about the code then we will break down on given task in project.
this should be il.invoice_id in both part of the query because thats the column we should be joining on.
Second,
As per the above portion of the code you calculated
total number of customers
total value of sales
average value of sales per customer
average order value
and then you did group by
So, what happens with the group, you get the table with the unique countries name, so when you call customer table to do CASE in the code below, it will not logically correct
because you are taking table reference FROM customer_country cc So, approach should be
Divide all the countries into two groups:
a) other
b) county with their name repeating more than one (non-others)
and then calculate the fields.
you will get the correct output.
Hope this helps!
Also, It will be great if you refer here and here regarding question formatting.
