May you kindly help me to realize where i went wrong in my code pls :)!

From the code I wrote below, I do not get the same results as the answer key. but I really do not understand why. !!! :frowning:

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

  1. Divide all the countries into two groups:
    a) other
    b) county with their name repeating more than one (non-others)
  2. 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.

1 Like