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)

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

5 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.

2 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.

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.

Hi @jamesberentsen,

Have you clicked on :arrow_forward: Query Explanation?

I have edited my post to specify that.

Best,
Sahil

1 Like

Thanks Sahil,

I see it now

1 Like