Count users that switch from plan A to plan B every month

I want to count all users that switch from ADVANCED plan to BASIC every month. So, the table plans contain the following data:

The expected result:

Here's my query:

SELECT COUNT(distinct userid) AS num_users, DATE_TRUNC('month',start_date) start_month
FROM plans
where plan_name ~ '\yADVANCED\y|\yBASIC\y'
GROUP BY DATE_TRUNC('month',start_date)
HAVING COUNT(userid) > 1 AND
SUM(
		CASE 
			WHEN ( plan_name ~ '\yADVANCED\y') OR ( plan_name ~ '\yBASIC\y') THEN 1
			ELSE 0
		END	
	) > 1
ORDER BY DATE_TRUNC('month',start_date) DESC;

The problem is how I can compare the start_date (in timestamp) of both plans, so I can figure out that there has been a switch of plans - Advanced -> Basic. Any insights or ideas are really appreciated.

DB fiddle with step-by-step queries

The query can be:

with plan_data as (
  select plan.*,
   LEAD(plan_name) OVER (PARTITION BY userId ORDER BY start_date) as next_plan
  from plan)

select count(distinct userid) as num_users, 
       to_char(plan_data.start_date, 'YYYY-MM') as year_month
from plan_data
where plan_data.plan_name = 'ADVANCED' and plan_data.next_plan = 'BASIC'
group by to_char(plan_data.start_date, 'YYYY-MM')
order by year_month;

Output:

Details:

1. plan_data query uses LEAD() window function: the whole table is divided into partititions via PARTITION BY userId and the rows inside each partition are ordered by start_date column.<br>
   So the LEAD() function for a current row gets the plan_name from the next row in a given partition.
2. As plan_data now contains both fields for old plan and next plan, we can simply filter the necessary rows and count the number of users with changed plans.
3. Switch date is the date when the plan was changed from 'ADVANCED' to 'BASIC'.

To identify users who have switched from the ADVANCED plan to the BASIC plan, you need to compare the current plan with the previous plan for each user. One way to achieve this is by using a self-join on the "plans" table, where you can join the table with itself based on the same user but with different start dates. Then, you can compare the plan_name for the current and previous records to check if there has been a switch.

Here's the modified query that includes a self-join and adds logic to count the users who switched from ADVANCED to BASIC each month:

 WITH plan_changes AS (
  SELECT
    p1.userId,
    p1.plan_name AS current_plan,
    p1.start_date AS current_start_date,
    p2.plan_name AS previous_plan,
    p2.start_date AS previous_start_date
  FROM plans p1
  LEFT JOIN plans p2
  ON p1.userId = p2.userId
  AND p1.start_date > p2.start_date
  WHERE p1.plan_name = 'BASIC'
  AND p2.plan_name = 'ADVANCED'
)
SELECT
  COUNT(DISTINCT userId) AS num_users,
  TO_CHAR(DATE_TRUNC('month', current_start_date), 'YYYY-MM') AS monthly_changes
FROM plan_changes
WHERE DATE_TRUNC('month', current_start_date) = DATE_TRUNC('month', previous_start_date)
GROUP BY DATE_TRUNC('month', current_start_date)
ORDER BY DATE_TRUNC('month', current_start_date) DESC;

Explanation of the query:

The plan_changes CTE (Common Table Expression) is used to get information about users who switched from ADVANCED to BASIC. The self-join is performed based on the userId and ensures that the current plan's start_date is greater than the previous plan's start_date.

In the main part of the query, we use the plan_changes CTE to count the number of distinct users who switched plans for each month. We use the TO_CHAR function to format the dates as "YYYY-MM" for better readability in the results.

The WHERE clause filters the results to include only those cases where the current_start_date and previous_start_date fall within the same month.

The final result is grouped by the month and ordered in descending order of months.

Now, the query should provide you with the expected result, showing the number of users who switched from ADVANCED to BASIC each month.

Try something along the lines of

WITH plan_changes AS (
    SELECT userid,
           plan_name,
           start_date,
           LAG(plan_name) OVER (PARTITION BY userid ORDER BY start_date) as prev_plan,
           DATE_TRUNC('month',start_date) as month
    FROM plans
)

SELECT COUNT(DISTINCT userid) AS num_users,
       month AS monthly_changes
FROM plan_changes
WHERE plan_name = 'BASIC' AND prev_plan = 'ADVANCED'
GROUP BY month
ORDER BY month DESC;