Screen Link:
My Code:
%%sql
CREATE VIEW top_countries AS
SELECT
c.country,
COUNT(DISTINCT c.customer_id) customers,
SUM(i.total) amount_sold,
SUM(i.total)/COUNT(*) avg_order,
SUM(i.total)/ COUNT(DISTINCT c.customer_id) avg_sales_per_customer
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
GROUP BY 1
ORDER BY 2 DESC
SELECT *
FROM (
SELECT
tc.*,
CASE
WHEN tc.customers > 1 THEN tc.country
ELSE 'Others'
END AS country
FROM top_countries tc
)
ORDER BY customers DESC;
What I expected to happen:
I expect the values in the ‘country’ column to update to ‘Other’ when customer count is <=1
What actually happened:
A new ‘country:1’ column is created
Hi guys, can someone help with the above? Been stuck for days