Step - 6 is quite challenging. I have been stuck on it for waay too long and wanted help regarding my approach so far.
WITH tracks_per_album AS ( SELECT album_id, COUNT(track_id) AS "track_count" FROM track GROUP BY 1 ), tracks_per_invoice AS ( SELECT il.invoice_id, t.album_id, COUNT(il.track_id) AS "track_count" FROM invoice_line AS "il" JOIN track AS "t" ON t.track_id = il.track_id GROUP BY 1,2 ) SELECT COUNT(*) FROM tracks_per_album AS "tpa" JOIN tracks_per_invoice AS "tpi" ON tpa.album_id = tpi.album_id WHERE (tpa.track_count = tpi.track_count)
The above will output 207. As per the solution, that should be 114.
An overview of my solution -
- I create
tracks_per_albumwhich stores the
album_idand the number of tracks in each album.
- I create
track, and it stores the
album_idand the number of tracks per invoice per album.
- I join the above two on their respective
- To get the number of invoices that are album purchase, I just check if the
track_countin both of those tables is the same or not. If it is, then it’s an album purchase.
Looking at a specific invoice
invoice_id = 4 is an album purchase as per my solution above, but as per Dataquest’s solution, it’s not.
Based on my solution above, if we look at
invoice_id = 4, I get -
Comparing the above to the number of tracks in the
album_ids listed in the above table -
Now, as you can see -
album_id = 260, the number of tracks is 1
- My current attempt, because of
album_id = 260, assumes that the
invoice_id = 4is an album purchase since the track count is 1
- Even though it’s not an album purchase because there are tracks from different albums that were also bought as per
invoice_id = 4
What I require help with
- How do I tackle the above case given my current implementation? Given the disparity in my count vs the official solution’s, there are definitely more invoices which fall under the same situation.
The above approach felt more intuitive to me than the one provided in the solution. When trying to solve the above, I also came across @Bruno’s suggestion here which, I think, is also talking about a similar approach.
I am just stuck on how to proceed forward from this, at the moment. Any feedback is appreciated. Thanks!