Seeking help on SQL Business Questions Project - Checking for Album vs Single purchases

Hi everyone,
I am reaching out for some help with conceptualization of the Albums vs Individual Tracks problem in the SQL Business Analysis Project.

I think that I understand how we are going to utilize the code that is given as part of the solution to the problem, but I cannot wrap my head around how we are going to obtain the two album track lists to compare to each other. I have looked through a few other user’s solutions. Although I can probably produce the solution based upon their code, I am unable to visualize why the code works.

So far, I have been trying to create two temporary views to compare to each other

CODE
----------------------------------------------
/* Create a view of tracks grouped by album */

WITH
     tracks_by_album AS
                (SELECT album.album_id AS album_id,
                        track.track_id AS track_id
                   FROM album
                  INNER JOIN track 
                        ON track.album_id = album.album_id
                  GROUP BY album.album_id),

------------------------------------------------
/* Create a view of tracks grouped by invoice */

   tracks_by_invoice AS
                (SELECT invoice.invoice_id AS invoice_id,
                        track.track_id     AS track_id,
                        track.album_id     AS album_id
                   FROM invoice
                  INNER JOIN invoice_line
                        ON invoice_line.invoice_id = invoice.invoice_id
                  INNER JOIN track
                        ON track.track_id = invoice_line.track_id
                  GROUP BY invoice.invoice_id)

My understanding is that this would create a view that looks like…

Diagram of tracks_by_album

tracks_by_album

album_id = 1
track_id = 1
track_id = n

album_id = n
track_id = 1
track_id = n
Diagram of tracks_by_invoice

tracks_by_invoice

invoice_id = 1
track_id = 1
track_id = n

invoice_id = n
track_id = 1
track_id = n

So if I understand correctly, I would then have to take those two views and compare them to each other element-wise? Something like:

CODE
SELECT CASE
            WHEN ((SELECT tracks_by_album.album_id 
                     FROM tracks_by_album
                   EXCEPT
                   SELECT tracks_by_invoice.invoice_id 
                     FROM tracks_by_invoice)
                       IS NULL)
                  
                   AND
                  
                 ((SELECT tracks_by_invoice.invoice_id 
                     FROM tracks_by_invoice
                   EXCEPT
                   SELECT tracks_by_album.album_id 
                     FROM tracks_by_album) 
                       IS NULL)
                   
                   THEN TRUE
                   ELSE FALSE
            END AS album_purchase
  FROM tracks_by_album
  LEFT JOIN tracks_by_invoice
       ON tracks_by_invoice.album_id = tracks_by_album.album_id

But these statements never evaluate to true. Could someone help me understand what views are being compared to what/how groupings are carried along when you are referring to a previously created view?

Hey man let me see if I can be of any assistance because at first the solution was confusing for me to.
I’ll put the walkthrough in this jupyter notebook. I may have made some formatting mistakes but if those inhibit you understanding the process just let me know.
Albums-vs-Individual-Tracks-help.ipynb (445.5 KB)

Click here to view the jupyter notebook file in a new tab

also just a tip if there’ s something you don’ t understand initially just keep reading through because maybe the next cell will provide more context

Thanks for your help, @Rawrgunz55. I think I understand now - just dealing with some bugs, then I think that my code will reach the right solution!

1 Like