HELP: Collecting countries with one customer into Other

I am a little stuck on Guided Project: Answering Business Questions using SQL and would love any direction on understanding the solution.

The part I am struggling with is this:

Write a query that collates data on purchases from different countries.

Where a country has only one customer, collect them into an "Other" group.

I am having trouble understanding how the CASE shown in the first section of code below helps to label countries with only one customer as ‘Other’ - specifically how the “WHERE country = c.country” clause works.

I have messed around with the code but cannot seem to work it out. The rest of the solution makes sense to me but I just can’t get my head around how to group and label the countries.

Solution:

WITH country_or_other AS
    (
     SELECT
       CASE
           WHEN (
                 SELECT count(*)
                 FROM customer
                 where country = c.country
                ) = 1 THEN "Other"
           ELSE c.country
       END AS country,
       c.customer_id,
       il.*
     FROM invoice_line il
     INNER JOIN invoice i ON i.invoice_id = il.invoice_id
     INNER JOIN customer c ON c.customer_id = i.customer_id
    )

SELECT
    country,
    customers,
    total_sales,
    average_order,
    customer_lifetime_value
FROM
    (
    SELECT
        country,
        count(distinct customer_id) customers,
        SUM(unit_price) total_sales,
        SUM(unit_price) / count(distinct customer_id) customer_lifetime_value,
        SUM(unit_price) / count(distinct invoice_id) average_order,
        CASE
            WHEN country = "Other" THEN 1
            ELSE 0
        END AS sort
    FROM country_or_other
    GROUP BY country
    ORDER BY sort ASC, total_sales DESC
    );

Thanks in advance!

Hello!

As I see it Where country = c.country is needed because without it the subquery SELECT count(*) FROM customer where country = c.country would only count the number of rows in the whole customer’s table and not only the rows witch have the same country.

The WHEN clause works almost like a ‘for’ in python it will iterate over c.country and the subquery will iterate over the rows in customer’s table. So when the rows iterations happend to have the same country as the outside c.country then they will count these rows. if the number of rows counted is one or bellow we will add this country at “Others”.

I’m sorry if my explanation is kinda lacking, I’m not a fluent english speaker and I’m also a beginner in DataScience so my explanation might be more confusing them helpful, but I hope it will at least help a little till a moderator comes to rescue you!