# 280-7 Albums vs Individual Tracks

Would a team member (not a student) mind going through the solution itself from top-to-bottom?

I just want to get the understanding of the approach towards the problem, why the method was chosen, how it was broken down and then how the components of the whole query were built/work together?

If so, that’d be very helpful in just understanding how to see these problems, as I imagine these would be the type on a regular basis on the job.

``````albums_vs_tracks = '''
WITH invoice_first_track AS
(
SELECT
il.invoice_id invoice_id,
MIN(il.track_id) first_track_id
FROM invoice_line il
GROUP BY 1
)

SELECT
album_purchase,
COUNT(invoice_id) number_of_invoices,
CAST(count(invoice_id) AS FLOAT) / (
SELECT COUNT(*) FROM invoice
) percent
FROM
(
SELECT
ifs.*,
CASE
WHEN
(
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)

EXCEPT

SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
) IS NULL
AND
(
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id

EXCEPT

SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
) IS NULL
THEN "yes"
ELSE "no"
END AS "album_purchase"
FROM invoice_first_track ifs
)
GROUP BY album_purchase;
'''

run_query(albums_vs_tracks)
``````
1 Like

Btw, is this really intermediate? Lol…it seems…advanced…at least to me.

Hi @ajtam555,

Somewhere around last year, I have attempted to explain the queries by breaking it down into smaller parts. However, it’s been a while, I am not sure whether it is correct or not. Please check it out and let me know whether it helps or not.
M191 Explained Solution.zip (922.8 KB) .

Make sure to click on `▶ Query Explanation` to understand how the query works.

Best,
Sahil

7 Likes

I wouldn’t put much stock into the name. Regardless, this query is hard for sure, don’t worry about it.

1 Like

Yeah - I agree. This section of SQL really went from 0-100 and some of the instructions are short/vague while the output is HUGE (I am talking about this one in particular). Personally for me, not fully knowing these queries is discouraging and you’re right, without a thought process to go along with it. It makes it hard for me to apply it in other places in the future.

6 Likes

At the same point myself.
I’ve successfully struggled through the first couple parts of the project. I’m glancing through Sahil’s solution, and while it helped a bit, I definitely would appreciate a bit of a breakdown with the logic.
I think I’m getting into a pattern of overthinking and not grasping what items we’re using EXCEPT on.

Thank you.

1 Like

Hi Sahil,

I looked in the ipynb file for explanation of the step which used except with case expression , but I did not see much explanation in there. It was the same things already in the mission.

Have you clicked on Query Explanation?

I have edited my post to specify that.

Best,
Sahil

1 Like

Thanks Sahil,

I see it now

1 Like

Hi Sahil, in the zip folder I see a bunch of sub folders or documents, but nothing I can just open and read with any standard applications. Does this need to be opened in a Jupyter application? Unsure how to do that.

1 Like

Welcome to the community!

Yes, it should be opened in the Jupyter app. The following screens explains how to install and open jupyter notebook:

Hope this helps

Best,
Sahil

1 Like

@Sahil this is great! I wish every dq guided project had similar commentary on the solution

2 Likes

Unfortunately I can’t agree that it explains much.
These are the beginning and end of the two text explanations in the file:

…let’s dive into understand `remaining_tracks`. We will explore from outer query to inner query by using abstract names.

{code}

Now that we have successfully understood, how that query works, let’s do some number crunching…

It feels like exactly between those two pieces the explanation is missing. It forces you to explore and figure out the code, instead of actually explaining it step by step. But it’s the only piece of code that trully requires explanation - all other pieces are much more understandable.

It’s just my opinion, of course