WITH playlist_info AS (SELECT p.playlist_id, p.name playlist_name, t.name track_name, (t.milliseconds / 1000) length_seconds FROM playlist p LEFT JOIN playlist_track pt ON p.playlist_id = pt.playlist_id LEFT JOIN track t ON t.track_id = pt.track_id ) SELECT playlist_id, playlist_name, COUNT(track_name) number_of_tracks, SUM(length_seconds) length_seconds FROM playlist_info GROUP BY 1,2 ORDER BY playlist_id ASC;
I initially used INNER JOIN (from the track table) in the code above and was getting a message saying “there are fewer options than there should be”
After getting frustrated with why, I looked at the answer and saw where I made the error - needed to do a join from the ‘playlist’ table and also use a LEFT JOIN.
Just wanted to confirm (for my understanding) if it’s because the task said to create a subquery of ALL the playlists.
Hope that makes sense.