Step - 6 is quite challenging. I have been stuck on it for waay too long and wanted help regarding my approach so far.
MY CODE:
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_album
which stores thealbum_id
and the number of tracks in each album. - I create
tracks_per_invoice
which joinsinvoice_line
andtrack
, and it stores theinvoice_id
, thealbum_id
and the number of tracks per invoice per album. - I join the above two on their respective
album_id
s. - To get the number of invoices that are album purchase, I just check if the
track_count
in 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 -
- For
album_id = 260
, the number of tracks is 1 - My current attempt, because of
album_id = 260
, assumes that theinvoice_id = 4
is 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!