SQLite CTE limitations (Correlated Subqueries)

Question

I'm confused why Correlated Sub Queries work fine in SQL, but CTE's that emulate the same logic with dependencies, throw an error that says 'XYZ CTE doesn't exist'.

Error message: Error while executing SQL query on database 'BB_Data': no such table: CTE2

I could of course persist these to a view, but these are temporary transformations hence why I've chosen a CTE.

Correlated Sub Query Approach which works fine

SELECT * ,
arrival_year||"-"||month_transformed||"-"||day_transformed as arrival_date
FROM(

SELECT *,
CASE WHEN length(arrival_month_numeric) =1 THEN "0"||arrival_month_numeric ELSE arrival_month_numeric END as month_transformed,
CASE WHEN length(arrival_date_day_of_month) =1 THEN "0"||arrival_date_day_of_month ELSE arrival_date_day_of_month END as day_transformed
FROM
(
SELECT *,
CASE 
WHEN arrival_month = 'January'  THEN 1
WHEN arrival_month = 'February'  THEN 2
WHEN arrival_month = 'March'  THEN 3
WHEN arrival_month = 'April'  THEN 4
WHEN arrival_month = 'May'  THEN 5
WHEN arrival_month = 'June'  THEN 6
WHEN arrival_month = 'July'  THEN 7
WHEN arrival_month = 'August'  THEN 8
WHEN arrival_month = 'September'  THEN 9
WHEN arrival_month = 'October'  THEN 10
WHEN arrival_month = 'November'  THEN 11
WHEN arrival_month = 'December'  THEN 12
ELSE 0 END as arrival_month_numeric
FROM
booking_table))

CTE Approach

WITH CTE1 AS(
SELECT *,
    CASE 
    WHEN arrival_month = 'January'  THEN 1
    WHEN arrival_month = 'February'  THEN 2
    WHEN arrival_month = 'March'  THEN 3
    WHEN arrival_month = 'April'  THEN 4
    WHEN arrival_month = 'May'  THEN 5
    WHEN arrival_month = 'June'  THEN 6
    WHEN arrival_month = 'July'  THEN 7
    WHEN arrival_month = 'August'  THEN 8
    WHEN arrival_month = 'September'  THEN 9
    WHEN arrival_month = 'October'  THEN 10
    WHEN arrival_month = 'November'  THEN 11
    WHEN arrival_month = 'December'  THEN 12
    ELSE 0 END as arrival_month_numeric
    FROM
    booking_table),

    CTE2 AS(
    SELECT *,
        CASE WHEN length(arrival_month_numeric) =1 THEN "0"||arrival_month_numeric ELSE arrival_month_numeric END as month_transformed,
        CASE WHEN length(arrival_date_day_of_month) =1 THEN "0"||arrival_date_day_of_month ELSE arrival_date_day_of_month END as day_transformed
    FROM CTE1)
    
    SELECT * FROM CTE2;