Help with CASE statement

SQL amateur can’t understand his own CASE statement game. Please help!

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/5/analyzing-sales-by-country

My Code:

%%sql

WITH country_info AS
                    (
                    SELECT
                     CASE 
                     WHEN   (SELECT
                               COUNT(customer_id)
                               FROM customer
                               GROUP BY country
                            ) = 1 THEN "Other"
                                ELSE c.country
                                END AS country, 
                    c.customer_id
                    FROM customer c
                    GROUP BY 1, 2
                    ORDER BY 1
                    ),
sort AS 
        (
        SELECT
            ci.*,
            CASE
                WHEN ci.country = "Other" THEN 1
                ELSE 0
            END AS sort
        FROM country_info ci
        )

SELECT
        country,
        customers,
        total_sales,
        average_order,
        customer_average_value
FROM (
        SELECT
                ci.country,
                count(distinct c.customer_id) customers,
                SUM(il.unit_price) total_sales,
                CAST(SUM(il.unit_price) AS FLOAT) / count(distinct c.customer_id) customer_average_value,
                CAST(SUM(il.unit_price) AS FLOAT) / count(distinct i.invoice_id) average_order
        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
        INNER JOIN country_info ci ON ci.customer_id = c.customer_id
        INNER JOIN sort s ON s.customer_id = ci.customer_id
        GROUP BY 1
        ORDER BY sort ASC, total_sales DESC
     );

What I expected to happen :
I expected to have a sorted table by countries using the ‘Other’ classification technique given by the mission as a way to group all countries with a single customer on a same category. I coded the classification and the sorting column as two separate With Statements, but found out that the code on my WITH statement:

%%sql
SELECT
         CASE 
         WHEN   (SELECT
                   COUNT(customer_id)
                   FROM customer
                   GROUP BY country
                ) = 1 THEN "Other"
                    ELSE c.country
                    END AS country, 
        c.customer_id
        FROM customer c
        GROUP BY 1, 2
        ORDER BY 1;

actually, classifies every country as an “Other”, and I don’t understand what’s the logic behind this bug.

What actually happened:

  • When classifying:
country customer_id
Other 1
Other 2
Other 3
Other 4
Other 5
Other 6
Other 7
Other 8
Other 9
Other 10
Other 11
Other 12
Other 13
Other 14
Other 15
Other 16
Other 17
Other 18
Other 19
Other 20
Other 21
Other 22
Other 23
Other 24
Other 25
Other 26
Other 27
Other 28
Other 29
Other 30
Other 31
Other 32
Other 33
Other 34
Other 35
Other 36
Other 37
Other 38
Other 39
Other 40
Other 41
Other 42
Other 43
Other 44
Other 45
Other 46
Other 47
Other 48
Other 49
Other 50
Other 51
Other 52
Other 53
Other 54
Other 55
Other 56
Other 57
Other 58
Other 59

*The final output:

country customers total_sales average_order customer_average_value
Other 59 4709.429999999431 7.670081433223829 79.82084745761748

I receive your feedback with many thanks and kind regards!

2 Likes

Hi @estebanalfaroorozco

I am deliberately not looking at the entire query. I will just start with this part: This is causing all rows to come as Other

You can first count the no. of customers in a given country in a nested query then apply an outer query which takes results from this query

For example: the inner query counts the customers and outer query tags those countries as “Others” where the count is 1.

%%sql
SELECT
    CASE 
     WHEN Count_of_customers <= 1 THEN "Others"
        ELSE country
    END as Country_name,
    Count_of_customers
FROM 
(
SELECT
    country,
    count(customer_id) as Count_of_customers        
FROM customer
GROUP BY country)

Observe the results and tweak according to what you think you can make a better solution.

1 Like