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
artist_name. These errors have been rectified.