#!/usr/bin/env python
# coding: utf-8

# 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]:


get_ipython().run_cell_magic('capture', '', '%load_ext sql\n%sql sqlite:///chinook.db\n')


# In[2]:


get_ipython().run_cell_magic('sql', '', "with album_track_count as (select a.album_id as alb_id, count(t.track_id) as track_count\n                     from album a\n                     inner join track t on a.album_id  = t.album_id\n                     group by 1),\n     one_album as (select i.invoice_id invo_id, count(distinct(album_id)) distinct_alum_count \n                   from invoice i\n                  inner join invoice_line il on i.invoice_id = il.invoice_id\n                  inner join track t on il.track_id = t.track_id\n                  group by i.invoice_id\n                  having count(distinct(album_id)) = 1),\n    one_album_tracks as (select o.invo_id invo_id, count(t.track_id) invoice_tracks\n                        from one_album o\n                        inner join invoice_line il on o.invo_id = il.invoice_id\n                        inner join track t on il.track_id = t.track_id\n                        group by 1),\n    segmented as (select i.invoice_id, case\n                  when o.invoice_tracks = a.track_count then 'Full Album'\n                  else 'Not Full Album'\n                  end as Full_Album\n                  from invoice i\n                left join one_album_tracks o on i.invoice_id = o.invo_id\n                inner join invoice_line il on i.invoice_id = il.invoice_id\n                inner join track t on il.track_id = t.track_id\n                inner join album_track_count a on t.album_id = a.alb_id\n                group by 1)\nselect full_album, count(invoice_id) as track_count, cast(count(invoice_id) as float)/(select count(invoice_id)\n                                                                                       from segmented) as percentage_of_tracks\nfrom segmented\ngroup by 1\n\n    \n                \n")


# In[ ]: