Guided Project: Answer to Albums vs. Singles without using the EXCEPT operator

Share Guided Project
, ,
#1

Screen Link:

I couldn’t figure out the EXCEPT operator so I ended up solving the problem without using it. I figured I would post it here in case someone gets stuck too!

My Code: 

%%sql

WITH invoice_albums AS 
    (
    SELECT
    i.invoice_id,
    COUNT(t.track_id) purchased_track_count,
    COUNT(distinct a.album_id) album_count
    FROM invoice i
    INNER JOIN invoice_line il ON il.invoice_id = i.invoice_id
    INNER JOIN track t ON t.track_id = il.track_id
    INNER JOIN album a ON a.album_id = t.album_id
    GROUP BY 1
    ),

    album_track_count AS
    (
    SELECT
        a.album_id,
        COUNT(t.track_id) track_count
        FROM album a
        INNER JOIN track t ON a.album_id = t.album_id
        GROUP BY 1
    ),

    first_track_count AS
    (
    SELECT
    il.invoice_id,
    MIN(il.track_id) first_track_id,
    atc.album_id,
    atc.track_count album_track_count,
    COUNT(il.track_id) purchased_track_count
    FROM invoice_line il
    INNER JOIN track t ON t.track_id = il.track_id
    INNER JOIN album_track_count atc ON atc.album_id = t.album_id
    GROUP BY 1
    )

SELECT
    CASE
        WHEN ia.album_count = 1 
        AND ftc.purchased_track_count = ftc.album_track_count
        THEN "Albums"
        ELSE "Singles"
    END AS purchase_type,
    COUNT(ia.invoice_id) number_of_invoices,
    ROUND(CAST(COUNT(ia.invoice_id) AS float)/ 
        (
            SELECT COUNT(*)
            FROM invoice
        ),2) percentage
FROM invoice_albums ia
LEFT JOIN first_track_count ftc ON ftc.invoice_id = ia.invoice_id
GROUP BY 1
    ;

Here is the output:

purchase_type number_of_invoices percentage
Albums 114 0.19
Singles 500 0.81
3 Likes
#2

@gdelaserre: recategorized your topic. The Except operator basically excludes to exclude certain criteria and is usually used with a subquery as shown here

2 Likes