Hello,
I’m finishing my 6. Albums vs Individual Tracks. But I encounter some logical errors in my code. The problem is, my EXCEPT statement gave me an unexpected output.
I created two columns that should be compared :
-album_track_count
-invoice_track_count
In theory, when both have the same value in a row, that means we have an “album” string in the purchase column. But in the very first row, the output is wrong: “tracks”
My code:
%%sql
DROP VIEW IF EXISTS invoice_album_track_count;
CREATE VIEW invoice_album_track_count AS
SELECT
il.invoice_id,
t.album_id AS invoice_album_id,
COUNT(il.track_id) AS invoice_track_count
FROM track AS t
INNER JOIN invoice_line AS il ON il.track_id = t.track_id
GROUP BY 1, 2;
DROP VIEW IF EXISTS all_tracks_from_album;
CREATE VIEW all_tracks_from_album AS
SELECT
album_id,
COUNT(track_id) AS album_track_count
FROM track AS t
GROUP BY 1;
DROP VIEW IF EXISTS compare_purchase_to_album;
CREATE VIEW compare_purchase_to_album AS
SELECT *
FROM all_tracks_from_album AS atfa
INNER JOIN invoice_album_track_count AS iatc ON iatc.invoice_album_id = atfa.album_id
ORDER BY invoice_id;
SELECT cpta.*,
CASE
WHEN (
SELECT cpta.album_track_count
FROM compare_purchase_to_album AS cpta
EXCEPT
SELECT cpta.invoice_track_count
FROM compare_purchase_to_album AS cpta
) IS NULL
AND
(
SELECT cpta.invoice_track_count
FROM compare_purchase_to_album AS cpta
EXCEPT
SELECT cpta.album_track_count
FROM compare_purchase_to_album AS cpta
) IS NULL
THEN "albums"
ELSE "tracks"
END AS purchase
FROM compare_purchase_to_album AS cpta
My output:
