Help understand SQL subquery

Screen Link:

My Code:

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

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

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 ccp.customer_id
             FROM customer_country_purchases ccp
             WHERE = 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 = 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


  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.

1 Like

Cool! This is helpful and adding a section on this topic would definitely be helpful as well. The visuals in some of earlier lessons were very helpful for me to more deeply understand some of these more abstract concepts. It might be helpful to include something like that. Thanks!