Challenge: Each Country's Best Customer - alternative solution

I have mixed feelings. I did the exercise slightly modifying the previous task. Everything worked.

WITH
    customers AS
                    (
                    SELECT *
                      FROM customer
                    ),
total_purchases_inv AS
                    (
                    SELECT 
                        i.customer_id,
                        SUM(i.total) AS sum_tot
                      FROM invoice AS i
                     GROUP BY i.customer_id
                    )
                    
SELECT c.country AS country,
       c.first_name || " " || c.last_name AS customer_name,
       MAX(tpi.sum_tot) AS total_purchased
  FROM customers AS c
 INNER JOIN total_purchases_inv AS tpi ON tpi.customer_id = c.customer_id
 GROUP BY 1
 ORDER BY 1 ASC;

I realise that this is probably not the best solution, as I was supposed to practice more complex versions of the code. But since mine works - and is also shorter (22 lines), why extend it( DQ answer: 38 lines?

When I read the task hints, I thought: I have no idea what the logic is behind them. :joy:
So I recalled myself my previous lessons and end up with this solution mentioned above.

Just finished analysing DQ answer (posted below):

WITH
    customer_country_purchases AS
        (
         SELECT
             i.customer_id,
             c.country,
             SUM(i.total) total_purchases
         FROM invoice i
         INNER JOIN customer c ON i.customer_id = c.customer_id
         GROUP BY 1, 2
        ),
    country_max_purchase AS
        (
         SELECT
             country,
             MAX(total_purchases) max_purchase
         FROM customer_country_purchases
         GROUP BY 1
        ),
    country_best_customer AS
        (
         SELECT
            cmp.country,
            cmp.max_purchase,
            (
             SELECT ccp.customer_id
             FROM customer_country_purchases ccp
             WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases
            ) customer_id
         FROM country_max_purchase cmp
        )
SELECT
    cbc.country country,
    c.first_name || " " || c.last_name customer_name,
    cbc.max_purchase total_purchased
FROM customer c
INNER JOIN country_best_customer cbc ON cbc.customer_id = c.customer_id
ORDER BY 1 ASC

I like the part WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases ) customer_id. I didn’t know that it’s allowed to use multiple = :thinking:
Besides of that, the DQ answer seems to be far from zen. What’s more, my approach seems to be more related to previous exercises.

I will be grateful for your comments.

2 Likes

The core of why DQ’s solution seems more complicated is this in your code -

Bruno goes into a lot more detail about why the above (having just one non-aggregated column in the GROUP BY clause) is not a good approach -

I recommend going through those (and also Why the need to over-complicate the queries by adding sub-queries?).

2 Likes

You are right. After I checked the links that you provided, now I understand why my approach isn’t good at all.
Thank you!

ps. I recommend to everybody to get familiar with these links!!!

1 Like

Thank you for posting this! I came up with a similar solution and didn’t understand why the proposed solution seemed so complex in comparison, but after checking those links it all makes sense now.

Perhaps there should be a lesson on why using the GROUP BY in this manner is bad practice? Unless it is already in a lesson and I just missed it, otherwise I would have had no idea :sweat_smile: