SQL: The question is a bit confusing!

Screen Link:
https://app.dataquest.io/m/189/intermediate-joins-in-sql/4/combining-multiple-joins-with-subqueries

My Code:

SELECT sq.title album, sq.name artist, COUNT(*) tracks_purchased
FROM invoice_line i JOIN
(SELECT t.track_id, ab.title, a.name
FROM track t
JOIN album ab ON
t.album_id = ab.album_id
JOIN artist a ON
a.artist_id = ab.artist_id) sq ON
i.track_id = sq.track_id
GROUP BY i.track_id
ORDER BY tracks_purchased DESC
LIMIT 5

What I expected to happen:

What actually happened:

Replace this line with the output/error

Question from DQ:
Write a query that returns the top 5 albums, as calculated by the number of times a track from that album has been purchased. Your query should be sorted from most tracks purchased to least tracks purchased

Solution from DQ:

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;

The difference between my code and DQ is GROUP BY. I understand the question is to count how many times a track has been purchased. Based on DQ’s GROUP BY, it is grouping album and artist. An album has multiple tracks, if we group like this, we are actually counting how many times an album has been purchased. I am confused here. Hope someone can help explaining.

This is not what the question is. The question is how many times an album had one of its tracks purchased.

The problem in your code is that you grouped by the tracks, not albums.

Also, Dataquest’s answer groups by albums and artists, but it would still work if you only group by album (in fact, that’s what I did), the output is the same.

1 Like

Thank you for your comment.
Well, I wouldn’t understand these 2 sentences the same.
“as calculated by the number of times a track from that album has been purchased” vs
“how many times an album had one of its tracks purchased”

DQ’s solution would make sense to me if the question is “how many times an album had one of its tracks purchased”

1 Like

oh sorry, I think I overlooked the first few words “a query that returns the top 5 albums”. Thank you :slight_smile:

1 Like

Hope the problem was solved.