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

Screen Link:

My Code:

WITH information AS
        COUNT(i.invoice_id) as purchase,,
        SUM( 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
        SUM(total) as country_total,
        SUM(purchase) as country_purchase,
        COUNT(customer_id) as num_of_customers,
            WHEN COUNT(customer_id) = 1 THEN 'other'
            ELSE country
        END AS new_country
        FROM information
        GROUP BY country
        ORDER BY country_total DESC
            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,
                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.


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.

1 Like

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 :roll_eyes:

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 *,
                     FROM customer c
                     WHERE country = = 1 THEN "Other"
            END AS new_country
    FROM customer c;


country_info AS
    (SELECT "Country",
        COUNT(DISTINCT c.customer_id) "Number of Customers",
        COUNT(DISTINCT i.invoice_id)"Number of Orders",
        ROUND(SUM(,2) "Total Sales",
        ROUND(SUM( c.customer_id),2) "Avg sales/customer",
        ROUND(SUM( 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 :slight_smile: