CODEWITHSUNDEEP
mysqlrecursioncommon-table-expression
Sannia Nasir
Sannia Nasir

Reputation: 1

cte table does not exist mysql

I am trying to calculate the number of users who have an active subscription on a particular day. I also want information related to the subscription plan they are on. I have a subscriptions table which includes the start date and end date of subscription as well as the plan name. I am using a recursive cte to find out the number of subscribers of different plans on a date range but I am getting the error that the cte table doesn't exist. I am using the following code.

SET @start = (SELECT MIN(start_date) FROM subscriptions);
SET @end = (SELECT MAX(end_date) FROM subscriptions);
WITH cte AS (
    SELECT @start dt
    UNION ALL
    SELECT date_add(dt, interval 1 day) FROM cte
    WHERE dt < @end
)

SELECT cte.dt, SUM(CASE WHEN subscriptions.plan_name IS NULL THEN 0 ELSE 1 END) FROM cte
LEFT JOIN subscriptions t 
ON cte.dt BETWEEN t.start_date AND t.end_date
GROUP BY cte.dt;

the output should look like this

Upvotes: 0

Views: 756

Answers (2)

Amir Khan
Amir Khan

Reputation: 27

Check for your mySQL version. CTE is supported from version 8 onwards

Upvotes: 0

Paul Spiegel
Paul Spiegel

Reputation: 31812

WITH cte AS (
    SELECT @start dt
    UNION ALL
    SELECT date_add(dt, interval 1 day) FROM cte
    WHERE dt < @end
)

You are refering cte in itself, which makes it recursive and needs to be defined as such (WITH RECURSIVE).

This is what you need:

WITH RECURSIVE cte AS (
    SELECT @start dt
    UNION ALL
    SELECT date_add(dt, interval 1 day) FROM cte
    WHERE dt < @end
)

Upvotes: 4

Related Questions