 # Help Needed: Guided Project: Answering Business Questions using SQL - 5. Analyzing Sales by Country

My Code:

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

1 Like

Not by much. This looks like it’s just rounding errors.

It is.

Your results look right to me and the differences seem to be attributed to rounding errors.

Hi @Bruno,

Thank you for the answer. When I checked the solution and the projects shared by others, I noticed that all of them joined an additional table invoice_line to use unit_price for the calculation. If I do the same I too get same output. That’s why I got confused whether am I missing something.

Maybe people are copying from the solution You’re good, don’t worry.

Hi everyone,

I have some doubts about this question in the guided project specifically, but I cannot understand what am I doing wrong in my particular case. My code so far is the following:

``````CREATE VIEW country_other AS
SELECT *,
CASE
WHEN
(SELECT
COUNT(c.customer_id)
FROM customer c
WHERE country = c.country) = 1 THEN "Other"
ELSE c.country
END AS new_country
FROM customer c;

WITH

country_info AS
(SELECT
c.country "Country",
COUNT(DISTINCT c.customer_id) "Number of Customers",
COUNT(DISTINCT i.invoice_id)"Number of Orders",
ROUND(SUM(i.total),2) "Total Sales",
ROUND(SUM(i.total)/COUNT(DISTINCT c.customer_id),2) "Avg sales/customer",
ROUND(SUM(i.total)/COUNT(DISTINCT i.invoice_id),2) "Avg order value"
FROM customer c
LEFT JOIN invoice i on c.customer_id = i.customer_id
GROUP BY Country
ORDER BY "Total Sales" DESC
)
``````

My original idea was to create a new view with the new_country column, where I can do the Country/Other organization, and then utilize this new view with country_info. I have run each separately, and I can achieve the right data from the country_info (but without the Other assignment) for now. However, when I look into my new view country_other, I don’t have any “Other” being assigned.

However, so far I could not understand the flaw in my logic, so if anyone could point me out to the (obvious) mistake, I would appreciate it 