Guided Project: Answering Business Questions using SQL --joins with subqueries

Q&A
#1 
%%sql
WITH t1 AS (
    SELECT
        COUNT(i.invoice_id) purchases, c.country, g.Name
    FROM invoice i
        JOIN customer c ON i.customer_id = c.customer_id
        JOIN invoice_line il ON il.invoice_id = i.invoice_id
        JOIN Track t ON t.track_id = il.track_id
        JOIN genre g ON t.genre_id = g.genre_id
    GROUP BY c.Country, g.Name
    ORDER BY c.Country, Purchases DESC
    )

SELECT t1.*
FROM t1
JOIN (
    SELECT MAX(Purchases) AS MaxPurchases, Country, Name
    FROM t1
    GROUP BY Country
    )t2
ON t1.Country = t2.Country
WHERE t1.Purchases = t2.MaxPurchases
ORDER BY 1 DESC;

I don’t understand this query.
I got the results from t1 , but do not see how it is filtered in the join .

I get that the filtering is done here

ON t1.Country = t2.Country
WHERE t1.Purchases = t2.MaxPurchases

But have not seen joins with subqueries before
SELECT t1.*
FROM t1
JOIN (

and the way the table looks is as if you should see all the results from t1 even though only the filtered results are returned.

As I understand everything before the JOIN should be returned, but it is not. Please explain?

#2

Hello,

The table below shows the order of execution for SQL queries.

So, the WHERE statement is executed before the SELECT statement. In the WHERE clause, we have basically said “Select all values from table 1 that have the same number of purchases as the MaxPurchases on table 2 that we have joined.”. Therefore, it returns the filtered results.

1 Like
#3

Thanks dilarakrby

1 Like