%%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
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?