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

Screen Link:
https://app.dataquest.io/m/191/guided-project%3A-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.

Thank you for your support.

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 :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 *,
        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 :slight_smile: