Enquiry regarding answer for Guided Project: Answering Business Questions using SQL

Screen Link:

One part of the answers was:

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

What does this part of the code mean?
SELECT count(*)
FROM customer
where country = c.country
) = 1 THEN “Other”

how would country=c.country allow you to sift through to find out the country with only count 1? Could someone help me out? Thank you!

This is a correlated subquery. https://www.wikiwand.com/en/Correlated_subquery#:~:text=In%20a%20SQL%20database%20query,query%2C%20it%20can%20be%20slow.

This query joins 3 tables. invoice_line, invoice, customer. invoice was joined solely to connect the other two tables, no columns were selected from it.
The information selected were country, customer_id, il.*. The 1st selection had a CASE to allow overwriting the default country name with ‘Other’ if there was only 1 country of it appearing in the customer table.

The WHERE statement in inner query is where the correlation happens. Correlated subquery means a piece of information from an outer query is used in an inner query. When the outer query goes down the rows and looks at the customer 1 by 1, a new country is supplied to the inner query. This country is used by WHERE in inner query to count how many of this country there are, to output either ‘Other’ or leave the name alone.

Yes I remember first looking at the solution and thought no way i could write this because the lessons never taught correlated subquery. There are probably ways to do it without though.