Answering Business Questions Using SQL 191-5

Screen Link:

My Code:

%%sql

WITH
    country_sales AS
    (
        SELECT 
            CASE
                WHEN COUNT(DISTINCT(c.customer_id)) = 1 THEN "Other"
                ELSE c.country
            END AS country,
            COUNT(DISTINCT(c.customer_id)) tot_cust,
            ROUND(SUM(i.total), 2) tot_sales,
            ROUND(SUM(i.total)/COUNT(DISTINCT(c.customer_id)), 2) avg_sales_per_cust,
            ROUND(SUM(i.total)/COUNT(i.invoice_id), 2) avg_order_value
        FROM customer c
        INNER JOIN invoice i ON i.customer_id = c.customer_id
        GROUP BY country
        ORDER BY tot_sales DESC
    ),
    sorting AS
    (
        SELECT 
            country,
            CASE
                WHEN country = "Other" THEN 1
                ELSE 0
            END sort
        FROM country_sales 
        GROUP BY country, sort
    )

SELECT cs.*
FROM country_sales cs
INNER JOIN sorting s ON s.country = cs.country
GROUP BY cs.country
ORDER BY s.sort, tot_sales DESC

What I expected to happen:
Countries that fall under “Other” to add up

What actually happened:

I’ve read a post where it says that I should change the alias name country but when I do that, in my country_sales, I get the error aggregate functions are not allowed in the GROUP BY clause. Is my country_sales doing too much at once and should be broken up into more subqueries? I’m not sure where to go so that the “Other” row comes out correctly.

Would appreciate some help on it, thank you!

1 Like

Hi @spi
It’s been a long time since I laid my hands on this project.
You’ve done a great job, the biggest problem here was in terms of how you understood the statement. Especially this part where I too would have done as you:

  • Where a country has only one customer, collect them into an “Other” group.

It would be better if you break your CTE into a subqueryl, that way, you can know how things are working.

Attached is the Solution Notebook which can help you further in demistifying the statements.