Analyzing Sales by Country: issue with updating country values

Q&A DQ Courses
, ,
#1

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 :slight_smile: :sweat_smile:

#2

Hey guys,

anybody who can help with this out there?

Many thanks,
Andrea

#3

Hi @moroa

Apologies. Missed to respond to you timely :frowning:

You have included tc.*, in your final query. Since the country column already exists as the first column, the CASE statement leads to a column with the same name. So SQL auto-updates that to the country1 column.

If you wish to have a country column first, write your case statement, followed by customer, avg (calculated) columns etc. instead of using tc.*.

Hope this works for you. Let us know otherwise.

#4

Thank you @Rucha. Sorry for the late response I’ve been out a few weeks.

‘Since the country column already exists as the first column, the CASE statement leads to a column with the same name. So SQL auto-updates that to the country1 column.’

Why doesn’t the same happen with the with statement? Because you are not creating a new entity inside the db?