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 =” 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.


WITH country_or_other AS
           WHEN (
                 SELECT count(*)
                 FROM customer
                 where country =
                ) = 1 THEN "Other"
       END AS country,
     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

        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,
            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!


As I see it Where country = is needed because without it the subquery SELECT count(*) FROM customer where 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 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 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!