(Potentially Dumb) General Question: Why do we need a subquery here?

Q&A DQ Courses
#1

As the title says, why do we even need a subquery here? Why can’t all of the code in the subquery just be a part of the main SELECT statement?

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 pt.playlist_id = p.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 1;
#2

Hi @s.cook20,

We need a subquery here, because we want to have the final table with only one row for each playlist, containing playlist id and name, and summarizing the number of tracks and their total duration for each playlist.

In the SELECT statement inside the WITH clause, instead, we create a table playlist_info with all the data necessary to obtain our final table (like track names to calculate the number of tracks and milliseconds to calculate the total duration). However, we don’t need to see this intermediate data itself.

1 Like