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

Hello everyone,

I have struggled a bit with the last question in guided project “Answering Business Questions Using SQL” as it is kind of complex & not straight forward, so decided to share the solution I came up with especially that eventually I was able to use the exception functionality.

One thing to highlight that for some reason I was always facing an error whenever I try to use WITH keyword, so I replaced it with a view creation.

  1. Get all the albums that were purchased in first place, to exclude the albums with zero sale:
    CREATE VIEW purchased_albums as
    SELECT DISTINCT album_id
    FROM invoice_line il
    INNER JOIN track tr
    ON il.track_id = tr.track_id;

  2. Get all the purchased albums with the corresponding tracks combined in a single view:
    CREATE VIEW purchased_albums_tracks as
    SELECT pa.album_id, tr.track_id
    FROM purchased_albums pa
    INNER JOIN track tr
    ON pa.album_id = tr.album_id;

  3. On the other side, Get all the invoice line items combined with the corresponding tracks:

CREATE VIEW invoice_tracks_combined as
SELECT ivl.invoice_id, tr.track_id, tr.album_id
FROM invoice_line ivl
INNER JOIN track tr
ON ivl.track_id = tr.track_id
GROUP BY ivl.invoice_id, tr.track_id;

  1. Stitch the invoice ID with all the albums_tracks_purchased, irrespective if it was purchased within the same invoice or not, this query is used as a subquery in the last query:

SELECT pat.album_id, pat.track_id, ivl_album.invoice_id
FROM purchased_albums_tracks pat
INNER JOIN (
SELECT invoice_id, album_id FROM invoice_tracks_combined
GROUP by 1,2 ) ivl_album
ON pat.album_id = ivl_album.album_id

  1. Consolidate all the queries to get the final answer to our question, apparently 80% of our customers have purchased single tracks, so what chnook is considering is feasible and would save cost!

%%sql

SELECT COUNT(DISTINCT invoice_id) singles,
(SELECT count(invoice_id) FROM invoice) - COUNT(DISTINCT invoice_id) as ’ albums’,
ROUND(CAST(COUNT(DISTINCT invoice_id) as float) / (SELECT count(invoice_id) FROM invoice), 2) as ‘%’
FROM
(
SELECT pat.album_id, pat.track_id, ivl_album.invoice_id
FROM purchased_albums_tracks pat
INNER JOIN (
SELECT invoice_id, album_id FROM invoice_tracks_combined
GROUP by 1,2 ) ivl_album
ON pat.album_id = ivl_album.album_id

EXCEPT

SELECT album_id, track_id, invoice_id
FROM invoice_tracks_combined
)

ANSWER:

Singles: 500
Albums: 114
Percentage: 81%

1 Like