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!