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 |