Near "WITH": syntax error

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!

I didn’t look at your code but I saw the error message point at the beginning. Did you leave out the “%%sql” part?

Hi Tony, I guess I didn’t copy the %%sql here but it is in my Jupyter notebook so that shouldn’t be the issue…
Thanks!

Hey.

Please review the syntax for WITH here.

Thanks! That’s what I needed!

1 Like