Screen Link:
https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/6/albums-vs-individual-tracks
My Code:
WITH invoice_with_album AS
(
SELECT
il.invoice_id,
a.album_id
FROM invoice_line il
LEFT JOIN track t ON il.track_id = t.track_id
LEFT JOIN album a ON t.album_id = a.album_id
)
WITH album_or_not AS
(
SELECT
invoice_id,
COUNT(distinct album_id) different_albums
FROM invoice_with_album
GROUP BY 1
)
SELECT
album,
number_of_invoices,
percentage_of_invoices
FROM
(
SELECT
CASE
WHEN different_albums = 1 THEN "Yes"
ELSE "No"
END AS album,
COUNT(invoice_id) number_of_invoices,
COUNT(album) / count(distinct invoice_id) percentage_of_invoices
FROM album_or_not aon
GROUP BY 1
);
What I expected to happen:
I expected a table with three columns and 2 rows.
What actually happened:
(sqlite3.OperationalError) near "WITH": syntax error
[SQL: WITH invoice_with_album AS
(
SELECT
il.invoice_id,
a.album_id
FROM invoice_line il
LEFT JOIN track t ON il.track_id = t.track_id
LEFT JOIN album a ON t.album_id = a.album_id
)
WITH album_or_not AS
(
SELECT
invoice_id,
COUNT(distinct album_id) different_albums
FROM invoice_with_album
GROUP BY 1
)
SELECT
album,
number_of_invoices,
percentage_of_invoices
FROM
(
SELECT
CASE
WHEN different_albums = 1 THEN "Yes"
ELSE "No"
END AS album,
COUNT(invoice_id) number_of_invoices,
COUNT(album) / count(distinct invoice_id) percentage_of_invoices
FROM album_or_not aon
GROUP BY 1
);]
(Background on this error at: http://sqlalche.me/e/e3q8)
Can somebody point me in the right direction? I’m kind of lost…
TIA!