Screen Link:
My Code:
%%sql
WITH country_other AS
(
SELECT
CASE
WHEN Count_of_customers <= 1 THEN "Others"
ELSE country
END as Country_name,
Count_of_customers,
il.*
FROM
(
SELECT
country,
count(c.customer_id) as Count_of_customers
FROM customer AS c
INNER JOIN invoice AS i on i.customer_id = c.customer_id
INNER JOIN invoice_line AS il on il.invoice_id = i.invoice_id
GROUP BY country))
SELECT
country_name,
Count_of_customers,
SUM(unit_price) AS total_sales,
SUM(unit_price) / Count_of_customers AS customer_lifetime_value,
SUM(unit_price) / COUNT(distinct invoice_id) AS average_order,
CASE
WHEN country_name = 'other' THEN 1
ELSE 0
END AS sort
FROM country_other AS co
GROUP BY country_name
ORDER BY sort ASC, total_sales DESC;
What I expected to happen:
What actually happened:
Replace this line with the output/error
Hi Everyone:
I have some questions would like to consult.
-
I tried a different approach from someone else idea with the case statement, but it seems I got an error with no invoice_line table. Could someone help me identify why il doesn’t show?
-
Also, I saw the answer shows select from invoice_line table, but since we group with the country so I wonder why don’t we select from customer table as the main table? I am still not too sure the order of the selection.
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
)
This is the answer code, and I want to ask the reason to use country=c.country is to perform a self join to check whether the number of customers is =1. I got confused with his so I tried an alternative method as shown in my first question.
Thank you so much for your help and assistance, much appreciate it.