Need help on Mission 280 - Guided Project: Answering Business Questions using SQL

Hi All -
I’m stuck on this guided project section. I wasn’t able to find my own solution and then when I looked at the solution code, I wasn’t able to understand why the code was solved this way and why it works.

Mission 280, Screen 5
Screen Link:

Here is the solution code:

sales_by_country = '''
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
)

SELECT
country,
customers,
total_sales,
average_order,
customer_lifetime_value
FROM
(
SELECT
    country,
    count(distinct customer_id) customers,
    SUM(unit_price) total_sales,
    SUM(unit_price) / count(distinct customer_id) customer_lifetime_value,
    SUM(unit_price) / count(distinct invoice_id) average_order,
    CASE
        WHEN country = "Other" THEN 1
        ELSE 0
    END AS sort
FROM country_or_other
GROUP BY country
ORDER BY sort ASC, total_sales DESC
);
'''

run_query(sales_by_country)

In the first subquery, they use SELECT COUNT(*) FROM customer WHERE country = c.country.

  1. Why is this particular syntax used? When I run that code in its own query, all I get is a single column with the value 59.

  2. I’m also not sure why the WHERE country = c.country is needed, although when I tried the code without this, it doesn’t change the countries with only 1 customer into “Other” then it doesn’t group them together.

  3. Then, I’m not sure how this works with the CASE statement and returns the value 1.

I understand that this is adding a new column that is adding up the distinct number of customers for each country, and then where there is only 1 customer for that country the value for the row in the new column is input as “Other”.

  1. I’m not sure exactly how the below works. From what I understand, we are supposed to use this to bring the “Other” row to the bottom of the list of countries, but I’m not sure how it does so.

    CASE WHEN country = “Other”
    THEN 1
    ELSE 0

I figured it out. the solution code was confusing to me in the way they integrate the subquery into the FROM clause of the last/main query. I feel like this would be better placed in the WITH AS section and then use the FROM clause.

I still don’t really understand why the country = c.country was needed in the course-provided solution code in the first subquery within a subquery.

Here’s my solution to the challenge:

country_statistics_query = '''
WITH country_customer_count 
     AS (SELECT customer.country, 
                COUNT(DISTINCT customer.customer_id) AS country_count 
           FROM customer 
          GROUP BY customer.country
         ), 
        
     country_statistics
     AS (SELECT CASE 
              WHEN ccc.country_count = 1 THEN "Other" 
              ELSE customer.country 
            END                                  AS countries_and_other, 
            COUNT(DISTINCT customer.customer_id) AS number_of_customers, 
            SUM(invoice.total)                   AS total_sales, 
            COUNT(DISTINCT invoice.invoice_id)   AS count_transactions,
            SUM(invoice.total) / 
            COUNT(DISTINCT customer.customer_id) AS avg_sales_per_customer,
            SUM(invoice.total) / 
            COUNT(DISTINCT invoice.invoice_id)   AS avg_sales_per_order,
            CASE 
              WHEN ccc.country_count = 1 THEN 1
              ELSE 0 
            END                                  AS sort_by_other 
       FROM customer 
            INNER JOIN country_customer_count AS ccc 
                    ON ccc.country = customer.country 
            INNER JOIN invoice 
                    ON invoice.customer_id = customer.customer_id
      GROUP BY countries_and_other
      ORDER BY sort_by_other ASC, 
               total_sales DESC
      )
               
SELECT countries_and_other    AS "Country", 
       number_of_customers    AS "Total Customers", 
       total_sales            AS "Total Sales", 
       avg_sales_per_customer AS "Average Revenue Per Customer", 
       avg_sales_per_order    AS "Average Revenue Per Order" 
  FROM country_statistics;
 '''
run_query(country_statistics_query)