In this notebook, I propose what I consider to be a much simpler solution to the problem posed in Intermediate SQL:Guided Project: Answering Business Questions Using SQL, Screen 6 than the solution suggested in the module itself and displayed in the Solution Sheet. The problem posed is determining when a full album has been purchased. The module itself suggests setting up tables, and running a double-except list comparison. As the module ackowledges, this is very conceptually confusing. I demonstrate that the same result can be achieved using much simpler SQL logic. Using basic 'GROUP BY' and 'COUNT' logic and some JOINs,I simply set up one table listing the full track count for every album, a second listing every invoice that only contains purchases from one album, and then see if the number of tracks for each invoice that only contains one album equals the total number of tracks for that album. The Solution Notebook confirms that my solution is entirely correct, and in my opinion, it is simpler, more elegant, and more readable.
%%capture %load_ext sql %sql sqlite:///chinook.db
%%sql with album_track_count as (select a.album_id as alb_id, count(t.track_id) as track_count from album a inner join track t on a.album_id = t.album_id group by 1), one_album as (select i.invoice_id invo_id, count(distinct(album_id)) distinct_alum_count from invoice i inner join invoice_line il on i.invoice_id = il.invoice_id inner join track t on il.track_id = t.track_id group by i.invoice_id having count(distinct(album_id)) = 1), one_album_tracks as (select o.invo_id invo_id, count(t.track_id) invoice_tracks from one_album o inner join invoice_line il on o.invo_id = il.invoice_id inner join track t on il.track_id = t.track_id group by 1), segmented as (select i.invoice_id, case when o.invoice_tracks = a.track_count then 'Full Album' else 'Not Full Album' end as Full_Album from invoice i left join one_album_tracks o on i.invoice_id = o.invo_id inner join invoice_line il on i.invoice_id = il.invoice_id inner join track t on il.track_id = t.track_id inner join album_track_count a on t.album_id = a.alb_id group by 1) select full_album, count(invoice_id) as track_count, cast(count(invoice_id) as float)/(select count(invoice_id) from segmented) as percentage_of_tracks from segmented group by 1
|Not Full Album||500||0.8143322475570033|