I’m still having some trouble on understanding the logic behind the queries that are involved in step 6. I made another post, but felt that maybe my question was too broad. I’ve been stuck here for awhile now and I’m thinking that going through small chunks of code that I don’t understand may help me get the broader concept.
%%sql SELECT ( SELECT track_id FROM invoice_tracks WHERE invoice_tracks.invoice_id = invoice_album.invoice_id ) FROM invoice_album
What I expected to happen:
I expected this code to output each track _id that was in my invoice_tracks table. Sort of like below, but only with the number from the track_id column
What actually happened:
It gave me this instead. I have no idea, what it is being told to do…
Full code snipped that I am trying to understand. I’ve bolded the portion that I’m not understanding no matter how many times I read the mission instructions and refer back to the code. This is where I need help understanding.
CREATE VIEW invoice_tracks AS SELECT i.track_id, t.name, invoice_id, t.album_id FROM invoice_line i INNER JOIN track t on i.track_id = t.track_Id ORDER BY i.invoice_line_id; CREATE VIEW albums AS SELECT a.album_id, a.title album_title, track_id, name track_name FROM track t INNER JOIN album a ON t.album_id = a.album_id ORDER BY a.album_id, track_id; %%sql CREATE VIEW invoice_album AS SELECT DISTINCT invoice_id, album_id FROM invoice_tracks; %%sql WITH invoice_purchase_type AS **(** ** SELECT DISTINCT ia.invoice_id invoice_id,** ** CASE ** ** WHEN ** ** (** ** SELECT track_id** ** FROM invoice_tracks ** ** WHERE invoice_id = ia.invoice_id** ** EXCEPT** ** SELECT track_id ** ** FROM albums** ** WHERE album_id = ia.album_id** ** ) IS NULL ** ** AND ** ** (** ** SELECT track_id ** ** FROM albums** ** WHERE album_id = ia.album_id** ** EXCEPT** ** SELECT track_id** ** FROM invoice_tracks ** ** WHERE invoice_id = ia.invoice_id** ** ) IS NULL THEN 'Album_purchase'** ** ELSE 'Track_purchase'** ** END AS purchase_type** ** FROM invoice_album ia** ** )** SELECT purchase_type, COUNT(*) number_of_invoices, ROUND(CAST(COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM invoice) * 100, 2) percent_of_invoices FROM invoice_purchase_type GROUP BY purchase_type;