Building and Organizing Complex Queries - The WITH Clause

#190 #190-3

Screen Link:

My Code:

WITH playlist_info AS
    (
    SELECT 
        playlist.playlist_id,
        playlist.name playlist_name,
        ptid.number_of_tracks,
        ptid.length_seconds
    FROM playlist
    LEFT JOIN 
        (SELECT
            playlist_id,
            COUNT(track.name) number_of_tracks,
            (SUM(track.milliseconds) / 1000.0) AS length_seconds
        FROM playlist_track
        LEFT JOIN track ON track.track_id = playlist_track.track_id
        GROUP BY playlist_id
        ) ptid
        ON ptid.playlist_id = playlist.playlist_id
    )
    
    
SELECT 
    playlist_id,
    playlist_name,
    number_of_tracks,
    length_seconds
FROM playlist_info
ORDER BY playlist_info.playlist_id;  

I expected the codes produce the same table as the answer does

There is a slight difference in the result from my codes, that under “number_of_tracks” column, the values where the playlist has no tracks, is blank. Whereas in the result table produced the answer, it is 0.

Below is provided answer from DQ:

WITH playlist_info AS
    (
     SELECT
         p.playlist_id,
         p.name playlist_name,
         t.name track_name,
         (t.milliseconds / 1000.0) 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;
1 Like

Hello @ChuBo ,

Note that playlist_track doesn’t have all playlist ids; the full playlist id is within the playlist table. Because you’re aggregating the value in the ptid subquery, the aggregate functions like COUNT will only work on the those playlist ids that are within playlist_track but it won’t count the missing playlist id like 2 or 4. Thus, when you join playlist table with the ptid table, the resulting table will be missing number_of_tracks and length_seconds i.e. they will be null.

You can group the table outside of ptid by modifying the code as follows:

WITH playlist_info AS
    (
    SELECT 
        playlist.playlist_id,
        playlist.name playlist_name,
        COUNT(ptid.name) number_of_tracks,
        SUM(ptid.length_seconds) length_seconds 
    FROM playlist
    LEFT JOIN 
        (SELECT
            playlist_id,
            track.name,
            (track.milliseconds/1000.0) length_seconds
        FROM playlist_track
        LEFT JOIN track ON track.track_id = playlist_track.track_id
        ) ptid
        ON ptid.playlist_id = playlist.playlist_id
    GROUP BY 1,2
    ORDER BY 1
    )
    
SELECT * FROM playlist_info;

But somehow, the grader doesn’t accept the result of the modified code in spite of the similar results.

Here’s a comparison of both results:

The modified code


Dataquest’s solution code


It’s probably because of the rounding of the floats which produced the slight difference in results.

Though, it’s possible there’s a logical error in the modified code. If you have the time, try running the modified code; see if it fits your expectation and whether the grader will accept it.

If it’s reproducible, I’ll send feedback to the Dataquest team.

Cheers.

3 Likes

Thank you very much!

I tried the modified answer but it is still not accepted.

From what I understand from your explanation , since I LEFT JOIN playlist with ptid on playlist_id, there are some missing values that the query assigns as NULL, that’s why when I used COUNT clause within ptid subquerry before, the playlist_info’s number_of_tracks contains NULL values also. Right?

1 Like

Yup, you got it right.

In a way, you counted before all playlist ids are available. You want to count with all playlist id and not just those in ptid.

If you observe the modified code, I didn’t change much except putting the GROUP BY in the main query and not inside the brackets for the ptid subquery. That essentially groups the fully joined table instead of just ptid.

Hope that helps.

2 Likes