I tried to complete Answering Business Questions using SQL: Albums vs. Individual Tracks without using Except clauses, and I’m off by 3.
I can’t figure out what I’ve done wrong. Do you all see where I’m messing up?
%%sql WITH invoice_tracks AS ( SELECT il.invoice_id, t.album_id, COUNT(il.quantity) number_of_tracks_invoice FROM track t INNER JOIN invoice_line il ON il.track_id=t.track_id INNER JOIN album a ON a.album_id=t.album_id GROUP BY il.invoice_id, t.album_id ORDER By il.invoice_id, a.album_id ), album_tracks AS ( SELECT a.album_id, count(t.track_id) number_of_tracks_album FROM album a LEFT JOIN track t ON t.album_id=a.album_id GROUP BY a.album_id ORDER BY a.album_id ), combined_tracks AS ( select it.invoice_id, at.album_id, at.number_of_tracks_album, it.number_of_tracks_invoice, CASE WHEN at.number_of_tracks_album=it.number_of_tracks_invoice AND at.number_of_tracks_album >= 2 THEN "yes" ELSE "no" END AS entire_album_purchase from album_tracks at LEFT JOIN invoice_tracks it ON it.album_id=at.album_id ) SELECT CAST (( SELECT COUNT(*) FROM combined_tracks WHERE entire_album_purchase="yes" ) AS float) / CAST(COUNT(DISTINCT invoice_id) AS float) percentage_entire_album FROM combined_tracks ;
What I expected to happen: I expected to get .18566775244299674 as the percentage of full album purchases
What actually happened: I got 0.18078175895765472 and found that I classified 111 purchases as purchasing the full album, while the solution found 114.
Replace this line with the output/error
Where am I going wrong?