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.

In [1]:
%%capture
%load_ext sql
%sql sqlite:///chinook.db
Out[1]:
'Connected: None@chinook.db'
In [2]:
%%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

    
                
Done.
Out[2]:
Full_Album track_count percentage_of_tracks
Full Album 114 0.18566775244299674
Not Full Album 500 0.8143322475570033
In [ ]: