Answering Business Questions Using SQL - one counry, one customer - logic error?

Hello,

I’m doing 5. Analyzing Sales by Country. The task includes a quote: “Because there are a number of countries with only one customer, you should group these customers as “Other” in your analysis.” But, my query doesn’t show any country like that.

My code:

%%sql
 
  DROP VIEW IF EXISTS one_country_cust;
CREATE VIEW one_country_cust AS
                                SELECT 
                                       c.country,
                                       COUNT(i.total) AS customer_count                                   
                                  FROM customer AS c
                                 INNER JOIN invoice AS i ON i.customer_id = c.customer_id
                                 GROUP BY 1
                                 ORDER BY 2 ASC;
                                
        
SELECT *
  FROM one_country_cust

Output:

Where is the bug? :thinking:

One customer can have multiple invoices. If you join the tables and then count the customers based on the total you will incorrectly get multiple customers per country for the countries where one customer has multiple invoices.

1 Like