Screen Link:
https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/6/albums-vs-individual-tracks
My Code:
WITH fti AS
(
SELECT il.invoice_id, MIN(il.track_id) first_track_id, t.album_id
FROM invoice_line il
JOIN track t ON
il.track_id = t.track_id
GROUP BY 1
)
SELECT fti.*, CASE WHEN
(
SELECT il.track_id
FROM invoice_line il
WHERE il.invoice_id = (SELECT invoice_id
FROM fti)
EXCEPT
SELECT t.track_id
FROM track t
WHERE t.album_id = (SELECT album_id
FROM fti)
) IS NULL
AND
(
SELECT t.track_id
FROM track t
WHERE t.album_id = (SELECT album_id
FROM fti)
EXCEPT
SELECT il.track_id
FROM invoice_line il
WHERE il.invoice_id = (SELECT invoice_id
FROM fti)
) IS NULL
THEN 'Yes' ELSE 'No' END
AS album_purchase
FROM fti
What I expected to happen:
What actually happened:
Replace this line with the output/error
If the code is written as follow, it will return what I expect. The only difference between two blocks of codes are at WHERE clauses. In my code, it seems like the comparison only happened with the first result from subqueries and applied for all 614 rows, while the correct code below loop through each result from subqueries. But I don’t understand why, I expected my code should compare with first result from subqueries then second, third, so on… Hope someone can help enlighten me on this! Thank you in advance.
WITH fti AS
(
SELECT il.invoice_id, MIN(il.track_id) first_track_id, t.album_id
FROM invoice_line il
JOIN track t ON
il.track_id = t.track_id
GROUP BY 1
)
SELECT fti.*, CASE WHEN
(
SELECT il.track_id
FROM invoice_line il
WHERE il.invoice_id = **fti.invoice_id**
EXCEPT
SELECT t.track_id
FROM track t
WHERE t.album_id = **fti.album_id**
) IS NULL
AND
(
SELECT t.track_id
FROM track t
WHERE t.album_id = **fti.album_id**
EXCEPT
SELECT il.track_id
FROM invoice_line il
WHERE il.invoice_id = **fti.invoice_id**
) IS NULL
THEN 'Yes' ELSE 'No' END
AS album_purchase
FROM fti