Hello there! I´ve been struggling with this exercise for a long time, and as I didn´t fully understand the solution that uses the EXCEPT, I tried another approach.
Link to Exercise/Step 6
Write a query that categorizes each invoice as either an album purchase or not, and calculates the following summary statistics:
- Number of invoices
- Percentage of invoices
My Code:
WITH invoice_album_tracks AS(
SELECT il.invoice_id, t.album_id, COUNT(il.track_id) tracks
FROM invoice_line il
INNER JOIN track t ON t.track_id=il.track_id
GROUP BY 1,2
),
tracks_per_album AS(
SELECT album_id, COUNT(track_id) tracks
FROM track
GROUP BY 1
)
SELECT
CASE
WHEN (iat.tracks=tpa.tracks) AND (iat.tracks > 1)
THEN 'Yes' ELSE 'No'
END AS album_purchase, COUNT(DISTINCT iat.invoice_id) num_invoices,
CAST(COUNT(DISTINCT iat.invoice_id)AS FLOAT)
/(SELECT COUNT(DISTINCT invoice_id) FROM invoice) percentage
FROM invoice_album_tracks iat
INNER JOIN tracks_per_album tpa ON tpa.album_id=iat.album_id
GROUP BY 1;
The first query generates a table with invoice_ids, album_ids, and the number of track_ids per purchase.
The second query generates a table with amount of tracks per album_ids.
Third and last, I mixed both queries to determine if and invoice_id purchase with its corresponding album_id has the same amount of tracks per album_id, in that case it´s a full album purchase.
What I expected to happen was to get the correct answer from the solution, which is:
What actually happened was that I got almost the same output, but with an error of 4 invoices:
Did I miss anything?
Thanks in advance!
