Guided Project: Answering Business Questions using SQL - yet another Analyzing Sales by Country question

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
    )

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 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 to test, 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! Thanks :smile:

Im not the best at SQL.
I edited your selection a bit to help for Country_Other.
Heres what I came up with (although probably to late to help):

%%sql
CREATE VIEW country_other AS
SELECT country,
COUNT(customer_id) AS number_of_customers,
CASE
WHEN COUNT(DISTINCT c.customer_id) = 1
THEN ‘Other’
ELSE Country
END AS new_country
FROM customer c
GROUP BY country;
#Then if you run the next bit you will see where those single markets are.
SELECT *
FROM country_other
ORDER BY country;