Answering Business Questions using SQL: Albums vs. Individual Tracks

Hi, All,

I tried to complete Answering Business Questions using SQL: Albums vs. Individual Tracks without using Except clauses, and I’m off by 3.

I can’t figure out what I’ve done wrong. Do you all see where I’m messing up?

Screen Link:

My Code:

%%sql
WITH 
invoice_tracks AS 
(
    SELECT il.invoice_id,
        t.album_id,
        COUNT(il.quantity) number_of_tracks_invoice
    FROM track t
    INNER JOIN invoice_line il ON il.track_id=t.track_id
    INNER JOIN album a ON a.album_id=t.album_id
    GROUP BY il.invoice_id, t.album_id
    ORDER By il.invoice_id, a.album_id
),
album_tracks AS
(
    SELECT a.album_id,
        count(t.track_id) number_of_tracks_album
    FROM album a
    LEFT JOIN track t ON t.album_id=a.album_id
    GROUP BY a.album_id
    ORDER BY a.album_id
),
combined_tracks AS
(
select it.invoice_id,
        at.album_id,
        at.number_of_tracks_album,
        it.number_of_tracks_invoice,
        CASE
            WHEN at.number_of_tracks_album=it.number_of_tracks_invoice 
            AND at.number_of_tracks_album >= 2 THEN "yes"
            ELSE "no"
            END 
        AS entire_album_purchase
from album_tracks at
LEFT JOIN invoice_tracks it ON it.album_id=at.album_id
)

SELECT  CAST ((
        SELECT COUNT(*)
        FROM combined_tracks
        WHERE entire_album_purchase="yes"
        ) AS float) / CAST(COUNT(DISTINCT invoice_id) AS float) percentage_entire_album
FROM combined_tracks
;

What I expected to happen: I expected to get .18566775244299674 as the percentage of full album purchases

What actually happened: I got 0.18078175895765472 and found that I classified 111 purchases as purchasing the full album, while the solution found 114.

Replace this line with the output/error

Where am I going wrong?

Hi @wrshif408ca78597c445,

Welcome to the community!

It appears to be a similar situation as this post:

Minor changes in the result don’t necessarily mean that your code is wrong. This is explained very well by @Bruno in the following post:

Best,
Sahil