So, the last exercise was even harder on my diminished proficiency and the key answers button is not functioning at the moment. Could you please review my code and explain if my logic is correct or in the contrary why?
# here a query for a table with all tracks from each album %%sql CREATE VIEW album_tracks AS SELECT t.track_id tracks, a.album_id album FROM album a INNER JOIN track t ON t.album_id = a.album_id GROUP BY 1 ORDER BY 2; # here a query for a table with all tracks from each album that were sold? %%sql CREATE VIEW invoice_album_tracks AS SELECT t.track_id tracks, a.album_id album FROM invoice i INNER JOIN invoice_line il ON il.invoice_id = i.invoice_id INNER JOIN track t ON t.track_id = il.track_id INNER JOIN album a ON a.album_id = t.album_id GROUP BY 1 ORDER BY 2; %%sql WITH comparison AS ( SELECT CASE WHEN # here a comparison of first, which tracks of albums were not sold, (SELECT tracks FROM album_tracks EXCEPT SELECT tracks FROM invoice_album_tracks) AND # and then which where sold and are on albums. (SELECT tracks FROM invoice_album_tracks EXCEPT SELECT tracks FROM album_tracks) THEN 1 ELSE 0 END AS album_purchases, # each one true on both terms is an album purchase and should show a 1 on each track t.track_id, i.invoice_id invoice FROM invoice i INNER JOIN invoice_line il ON il.invoice_id = i.invoice_id INNER JOIN track t ON t.track_id = il.track_id ORDER BY 3, 2 ) # finally, a sum and a percentage of album purchases SELECT SUM(album_purchases) album_purchases, CAST(SUM(album_purchases) AS FLOAT) / COUNT(DISTINCT invoice) percentage_album_purchases FROM comparison;
What I expected to happen:
The answer button or Key button for the answers on this screen is not functional or available at the time. So, could you please answer me if my results are correct?
What actually happened:
My final output was:
Also, I dont know if the output for this
SELECT tracks FROM invoice_album_tracks EXCEPT SELECT tracks FROM album_tracks
EXCEPT statement is useful for the general query, as it outputs no values at all.
I receive your feedback with many thanks and kind regards!