Practicing some SQL from Danny Ma's case studies. Working on number 3, found here. The challenge question that I'm requires an iterative table of payments made during 2020.

I'm scaffolding up to my final solution by just creating a table with the payments from the basic_monthly plans with a recursive CTE. I've never done a recursive CTE before, and am struggling to find my error, which says: 'Query Error: relation "basic_monthly_payments does not exist'. Any thoughts? Thanks for your help!

WITH 
subscription_info AS ( 
    SELECT
        s.customer_id,
        s.plan_id,
        p.plan_name,
        s.start_date,
        LEAD(s.start_date) OVER(
                PARTITION BY s.customer_id
                ORDER BY s.customer_id
            ) AS next_date,
        p.price
    FROM
        subscriptions AS s
            LEFT JOIN
                plans AS p
                    ON s.plan_id = p.plan_id
    WHERE
        p.plan_id != 0 AND
        EXTRACT(YEAR FROM start_date) = '2020' ),

basic_monthly_payments AS ( 
    SELECT
        customer_id,
        plan_id,
        plan_name,
        start_date AS payment_date,
        price AS amount
    FROM
        subscription_info
    WHERE
        plan_id = 1
    
    UNION ALL
    
    SELECT
        customer_id,
        plan_id,
        plan_name,
        payment_date + INTERVAL '1 month' AS payment_date,
        amount
    FROM
        basic_monthly_payments
    WHERE
        payment_date + INTERVAL '1 month' < LEAST('01-01-2021'::date, next_date)
    ORDER BY
        1, 4
  )
SELECT
    *
FROM
    basic_monthly_payments
;

I did a lot of research online and have tried to follow others' models, but I am still getting the same error. If I put RECURSIVE in front of the basic_monthly_payments CTE, it's throwing a different error:

Query Error: syntax error at or near "basic_monthly_payments"

Practicing some SQL from Danny Ma's case studies. Working on number 3, found here. The challenge question that I'm requires an iterative table of payments made during 2020.

I'm scaffolding up to my final solution by just creating a table with the payments from the basic_monthly plans with a recursive CTE. I've never done a recursive CTE before, and am struggling to find my error, which says: 'Query Error: relation "basic_monthly_payments does not exist'. Any thoughts? Thanks for your help!

WITH 
subscription_info AS ( 
    SELECT
        s.customer_id,
        s.plan_id,
        p.plan_name,
        s.start_date,
        LEAD(s.start_date) OVER(
                PARTITION BY s.customer_id
                ORDER BY s.customer_id
            ) AS next_date,
        p.price
    FROM
        subscriptions AS s
            LEFT JOIN
                plans AS p
                    ON s.plan_id = p.plan_id
    WHERE
        p.plan_id != 0 AND
        EXTRACT(YEAR FROM start_date) = '2020' ),

basic_monthly_payments AS ( 
    SELECT
        customer_id,
        plan_id,
        plan_name,
        start_date AS payment_date,
        price AS amount
    FROM
        subscription_info
    WHERE
        plan_id = 1
    
    UNION ALL
    
    SELECT
        customer_id,
        plan_id,
        plan_name,
        payment_date + INTERVAL '1 month' AS payment_date,
        amount
    FROM
        basic_monthly_payments
    WHERE
        payment_date + INTERVAL '1 month' < LEAST('01-01-2021'::date, next_date)
    ORDER BY
        1, 4
  )
SELECT
    *
FROM
    basic_monthly_payments
;

I did a lot of research online and have tried to follow others' models, but I am still getting the same error. If I put RECURSIVE in front of the basic_monthly_payments CTE, it's throwing a different error:

Query Error: syntax error at or near "basic_monthly_payments"

• sql
• postgresql
• recursive-query
• recursive-cte

• 1 If you want to reference basic_monthly_payments from itself -- in effect creating a Recursive CTE -- then you need to abide with the SQL Standard and use WITH RECURSIVE instead of just plain WITH. The RECURSIVE clause qualifies the WITH clause, not a specific CTE, so it needs to be included at the very beginning. – The Impaler Commented Jan 29 at 20:48

1 Answer 1

Once you get past the recursive errors there are other errors as well :

• WITH subscription_info
• Order by in Recursive is not supported(see a similar answer for the reasoning)
• next_date is not part of basic_monthly_payments CTE but using in UNION
• And because next_date is added in basic_monthly_payments , it needs to be added in UNION ALL as it expects same number of columns.

Example fiddle => This query runs but unsure about data.