Help understand SQL subquery

Screen Link:
https://app.dataquest.io/m/190/building-and-organizing-complex-queries/8/challenge-each-countrys-best-customer

My Code:

WITH alias AS
    (SELECT
    customer_id,
    SUM(total) total
    FROM invoice i
    GROUP BY 1)

SELECT
    c.country country,
    c.first_name || ' ' || c.last_name customer_name,
    MAX(a.total) total_purchased
FROM customer c
INNER JOIN alias a ON c.customer_id = a.customer_id
GROUP BY c.country;

I came up with the above code for the challenge and it gave the correct output. The answer code is more complex, though.
My first question: What did I miss here? Why is my code giving the correct output?

Secondly, in the answer code, I couldn’t understand the purpose of the following subquery and its code.

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
        )

Please help.

Congratulations on picking up on the possibility that something might be wrong; There is indeed an issue.

This question has been asked multiple times in the past. Here is an answer. I recommend you read this answer the links on it.

When you say subquery, are you referring to counter_best_customer? That’s actually not a subquery, but a common table expression (you can think of it as a temporary table).

The purpose of this subquery is to identify (by fetching their id), the best customer for each country. The subquery in this CTE determines the customers whose total equals the total of the best customer.

Let me know if this is enough.

Hi Bruno! As far as I can tell, the answer for this problem is the first time we have seen a WHERE clause that compares two columns. Up until now we have only seen the WHERE clause used with a value, as in WHERE country == 'USA' or WHERE max_purchase > 2000.

I know that this subquery is producing the customer id with the highest purchase total for each country, but I dont understand how it works.

WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases

This concept shows up a couple times in the guided project as well and has ground my progress to a halt. Lol.
Thanks for your help!

edit - We have also seen WHERE [column] IN [subquery] and WHERE [column] > [aggregate] etc. Not trying to diminish what has been taught. I just dont know what WHERE [column] = [column] produces or how to use it.

Hey, Nick. Sorry for the late response.

Thank you for bringing this to our attention. I’m actually writing some new SQL content and I at least vaguely try to teach the very concept you’re asking about there.

When you compare two columns, what you’re doing is comparing the values of those columns for a given row.

Consider the product table below (which I just made up):

product_id price discount_price
1 3 2
2 3 3
3 9 8

Running…

SELECT *
  FROM product
 WHERE price = discount_price;

… will iterate over all the rows, compare the value in price with the value in discount_price for the corresponding row and save it if satisfies the condition. The code, therefore, outputs:

product_id price discount_price
2 3 3

I hope this helps.