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
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!