SELECT al.title album, ar.name artist, sum(il.quantity) tracks_purchased
FROM invoice_line il
inner join track t on t.track_id = il.track_id
inner join album al on al.album_id = t.album_id
inner join artist ar on ar.artist_id = al.artist_id
group BY 1,2
order by 3 DESC
LIMIT 5
The above is my code, and it outputs the correct results. no subqueries.
Below is the answer code, with subqueries.
SELECT
ta.album_title album,
ta.artist_name artist,
COUNT(*) tracks_purchased
FROM invoice_line il
INNER JOIN (
SELECT
t.track_id,
al.title album_title,
ar.name artist_name
FROM track t
INNER JOIN album al ON al.album_id = t.album_id
INNER JOIN artist ar ON ar.artist_id = al.artist_id
) ta
ON ta.track_id = il.track_id
GROUP BY 1, 2
ORDER BY 3 DESC LIMIT 5;
For all practical measures for the result (answer the posed question) they are equivalent. for the purposes of the lesson it’s required to used subqueries.
My concern is; time and again I find myself finding easy ways to form things with no subqueries, and I am wondering if that’s a good habit or not.
edit; figured out I had additionally mislabeled tracks_purchased
as quantity
and artist
as artist_name
. These errors have been rectified.