Question with Case and Table Error

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.

  1. 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?

  2. 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.

Hi @taojian2009

You neither have attached the link to nor used tags for the course/mission page. So not sure what the question was.
I will attempt to answer your questions nonetheless. Let the community know in case things go complete bouncer.

  1. No where in your query you have a table/sub-query which has been aliased as il.

  2. & 3. I am not sure but I think both these questions are connected. If you look at the INNER JOIN part, we are sort of filtering out the customers ID’s based on whether any Invoice has ever been generated against them.

Think of it this way, I may add customer ID’s in customer table and assign them ID’s. But they haven’t yet purchased anything from the store. So there will no valid Invoices against them.

So the INNER JOIN restricts the invoice ID’s which do have customer ID’s associated with them. So if the store opened recently for Singapore and no customers have yet purchased anything there, that Country would not show up in the Country group itself.

Hi Rucha:

Many thanks for the reply.

Sorry I didn’t make it clearer.

The project is Guided Project: Answering Business Questions Using SQL (191-5).

I am not really following on the 1st question. I created il because I defined invoice_line as il in the bottom along with customer and invoice table. So I would expect to show the whole invoice_line table.

Also, let me rephrase my questions for 2 and 3.

For question 2, I am not too sure about the priority of joining, as for this exercise, I believe the customer table is more important than the invoice and invoice_line table.

For question 3, I just not too sure why country is equal to country again and performed an inner join.

Thank you for the help. Let me know if I need to further clarify.

GET FREE LIFETIME ACCESS

Connections status

User menu