cte table does not exist mysql

SanniaNasir
October 28, 2022
44 views
0 votes
2 Answers

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

Answers

- PaulSpiegel
- October 28, 2022 at 3:40 pm
- 0 votes
0
```
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
)
```
Login or Signup to reply.