I have cheated a little bit on this project and I got a different result than the answer, so maybe my cheat was not that great. In general I’ve been banging my head against the wall with SQL, I get the syntax, but I don’t know how to use it (yet!). I wonder how you would fix my ■■■■■■ solution.
WITH albums_invoice AS
(
SELECT il.invoice_id,
COUNT(DISTINCT t.album_id) different_albums
FROM invoice_line il
JOIN track t ON t.track_id = il.track_id
GROUP BY 1
),
albums_invoice_def AS
(
SELECT *,
CASE
WHEN different_albums == 1 THEN "Album"
ELSE "Singles"
END AS album_singles
FROM albums_invoice
)
SELECT album_singles,
COUNT(invoice_id) count,
COUNT(invoice_id) * 100.0 / SUM(COUNT(invoice_id)) OVER () AS Percentage
FROM albums_invoice_def
GROUP BY 1
album_singles count Percentage
Album 171 27.850162866449512
Singles 443 72.14983713355049
Unfortunately there are some albums with one or two songs on it, which are basically singles as well. The total number of those is 90. But of course not every one of those albums has been purchased, so I can’t hack by simply deducting from the histogram. I wrote this code, but I can’t seem to integrate it anywhere in my original. Do I really have to work with EXCEPT then?
WITH tracks_per_album AS
(
SELECT album_id,
COUNT(track_id) tracks
FROM track
GROUP BY 1
)
SELECT tracks,
COUNT(album_id)
FROM tracks_per_album
GROUP BY 1
Apologies if I have completely misunderstood your post. I am confused about the below query.
This query might result in a scenario like this:
Album X has 10 tracks - “Customer A” purchases album X only - so complete album for that invoice - classified as “album”
Album Y & Z have 4 & 5 tracks respectively - “Customer B” purchases 3 tracks from Y and all the 5 tracks from Z - but this will get classified as “singles”, since for this invoice Count(Distinct(album_id)) > 1 (one for Y and one for Z) even though Z album has been purchased as a full album.
Perhaps why your results are so off from the solution.
Try to tackle this query this way:
Count the no. of tracks in each album. At this point, we don’t care if they are purchased or not-purchased. We just want to know how many tracks are associated with a given album (and this data is best not taken from invoice_line table)
Decide if you want to include the albums which only have 1 or 2 songs with them. Having or Where Clauses (or solution has MIN() applied) are options here
Count the tracks purchased per album per invoice. For example:
invoice_id
album_id
track_count
A
X
10
B
Y
3
B
Z
5
Match the count of tracks in 3rd with that of 1. based on your decision in 2. If the count of tracks in 1. and 3. match - full album; if not - single tracks were selected.
The 3. of course may not be this straightforward like it looks here (I wish!).
If you didn’t get any of this no worries, I took my time to break this code too and I will try to respond in a better way.
this post has an answer too a bit different from solution so only take a look if you wish to - here
After @Rucha’s explanation, it’s clearer. But during this whole section, I feel like all SQL missions that have more than 3 tables need a zoom meeting, texts are just so inefficient.
While the DQ class form is awesome, I personally think videos may help greatly in introducing SQL missions specifically.
-----------------------------------------------------------edits below------------------------------------------------------------
Just finished the project, managed to ‘cheat’ around EXCEPT, with a side-effect of hair loss…
There might be ways to clean it up a bit but I can’t be bothered for now, here’s my code:
%%sql
WITH
track_album AS
(SELECT t.album_id,
COUNT(t.track_id) track_num
FROM track t
INNER JOIN album a ON a.album_id = t.album_id
GROUP BY 1),
orders AS
(SELECT i.invoice_id,
a.album_id,
COUNT(il.track_id) track_num
FROM invoice i
INNER JOIN invoice_line il ON il.invoice_id = i.invoice_id
INNER JOIN track t ON t.track_id = il.track_id
INNER JOIN album a ON a.album_id = t.album_id
GROUP BY 1,2),
purchase_cat AS
(SELECT CASE
WHEN o.track_num = ta.track_num THEN 'album'
ELSE 'single'
END AS purchase_cat,
o.invoice_id
FROM orders o
INNER JOIN track_album ta ON ta.album_id = o.album_id),
edge_free_cat AS
(SELECT CASE
WHEN COUNT(DISTINCT(purchase_cat))= 2 THEN 'single'
ELSE purchase_cat
END AS purchase_cat,
invoice_id
FROM purchase_cat
GROUP BY invoice_id)
SELECT purchase_cat,
COUNT(invoice_id) amount,
ROUND(CAST(COUNT(invoice_id) AS FLOAT)/ (SELECT COUNT(invoice_id) FROM edge_free_cat),2) percentage
FROM edge_free_cat
GROUP BY purchase_cat
result:
purchase_cat
amount
percentage
album
114
0.19
single
500
0.81
I know it’s not as neat as the solution, but I came up with this and it makes a bit more sense to me personally.
In the meantime while preparing my portfolio I actually manage to fix it!
%%sql
WITH albums_invoice AS
(
SELECT il.invoice_id,
COUNT(DISTINCT t.album_id) different_albums,
COUNT(DISTINCT t.track_id) amount_tracks
FROM invoice_line il
JOIN track t ON t.track_id = il.track_id
GROUP BY 1
),
albums_invoice_def AS
(
SELECT *,
CASE
WHEN different_albums == 1 AND amount_tracks > 5 THEN "Album"
ELSE "Singles" -- #The line above filters for invoices containing only 1 album id and at least 6 tracks
END AS album_singles
FROM albums_invoice
)
SELECT album_singles,
COUNT(invoice_id) count,
COUNT(invoice_id) * 100.0 / SUM(COUNT(invoice_id)) OVER () AS percentage
FROM albums_invoice_def
GROUP BY 1
Like I was so close all the time, and I must say, after a lot of python, you also get the hang of SQL. Just go on that steep learning curve, it’s okay not to get everything rightaway!
Even though I am still ‘assuming’ that 5 tracks with the same album ID are albums. They actually say this in the exercise, that these edge cases of people buying a lot of tracks from the same album instead of buying the album hardly happens.
Great work! Your code is so much more concise than mine! I just realized there’s absolutely no need to join the invoice table after reading your first temp table. Duh. I will take that as a sign to take a break.
And thanks for the encouragement! Makes me feel better about the spaghetti both in my code and head.
Btw, I can totally see this post the start of EXCEPT haters club… lol
You know, this was as hard as it gets for SQL, there is nothing harder than this question in this website
EXCEPT
No, no, we don’t go there
By the way my code does miss the mark by 17 albums compared to the answer, yours is the answer, so I’m not certain who wins, you worked harder. That’s for sure.
