Hello there! I´ve been struggling with this exercise for a long time, and as I didn´t fully understand the solution that uses the EXCEPT, I tried another approach.

Write a query that categorizes each invoice as either an album purchase or not, and calculates the following summary statistics:

• Number of invoices
• Percentage of invoices

My Code:

``````WITH invoice_album_tracks AS(
SELECT il.invoice_id, t.album_id, COUNT(il.track_id) tracks
FROM invoice_line il
INNER JOIN track t ON t.track_id=il.track_id
GROUP BY 1,2
),

tracks_per_album AS(
SELECT album_id, COUNT(track_id) tracks
FROM track
GROUP BY 1
)

SELECT
CASE
WHEN (iat.tracks=tpa.tracks) AND (iat.tracks > 1)
THEN 'Yes' ELSE 'No'
END AS album_purchase, COUNT(DISTINCT iat.invoice_id) num_invoices,
CAST(COUNT(DISTINCT iat.invoice_id)AS FLOAT)
/(SELECT COUNT(DISTINCT invoice_id) FROM invoice) percentage
FROM invoice_album_tracks iat
INNER JOIN tracks_per_album tpa ON tpa.album_id=iat.album_id
GROUP BY 1;
``````

The first query generates a table with invoice_ids, album_ids, and the number of track_ids per purchase.
The second query generates a table with amount of tracks per album_ids.
Third and last, I mixed both queries to determine if and invoice_id purchase with its corresponding album_id has the same amount of tracks per album_id, in that case it´s a full album purchase.

What I expected to happen was to get the correct answer from the solution, which is:

What actually happened was that I got almost the same output, but with an error of 4 invoices:

Did I miss anything?

have you checked these posts for any differences?

Hi there @Rucha!
Yes, I read every single topic on this exercise to understand the solution and also to get alternative ways to solve it. But I couldn´t find any topic with the 4 invoices difference from the solution as I got.
Thanks for the feedback!

Hi there!

I’m not 100% sure this will help, but just some ideas from what I remember when working on this solution. Disclaimer: I also didn’t get the exact solution, but it might get you a tiny bit closer!

@Rucha also helped me with my solution, and they used the conditional for albums with more than 2 tracks. In Python, this translated to Having > 2. Here, it looks like you only exclude albums with only 1 track (iat.tracks > 1). Perhaps try changing that condition to > 2?

1 Like

Hello @khmidah!
Yes, I already tried changing the condition to >2 but the result remains the same

Thing is I got the same answer as you do with my stupid and overly confusing query, and I didn’t debug it at that time, to match it exactly like the solution. I took the lazy-learner’s-attitude and parked it for later (that never came I guess)

Here’s an explanation by @Sahil for the solution - 280-7 Albums vs Individual Tracks