如何统计每月从ADVANCED切换至BASIC套餐的用户数量？

需求 #

统计每月所有从ADVANCED套餐切换到BASIC套餐的用户数量。

示例数据（plans表） #

预期结果 #

现有问题 #

原SQL仅通过当月套餐数量和包含两种套餐来判断切换，但未对比同一用户套餐的时间顺序，无法准确识别从ADVANCED切换到BASIC的行为（比如用户当月多次续订ADVANCED会被误判）。

解决思路 #

要准确统计切换行为，核心是追踪同一用户的套餐时间线，找到每个用户最新的ADVANCED套餐之后出现的BASIC套餐，以此作为有效切换记录，再按切换发生的月份统计。

具体步骤： #

• 为每个用户的套餐记录按start_date降序排序，标记每个用户的套餐顺序；
• 筛选出用户套餐序列中，前一个套餐是ADVANCED、当前套餐是BASIC的记录——这就是有效切换；
• 按BASIC套餐的起始月份分组，统计去重后的用户数。

修正后的SQL（PostgreSQL为例） #

WITH user_plan_order AS (
    SELECT
        userId,
        plan_name,
        start_date,
        -- 按用户分组，按时间降序给套餐排号，最新的为1
        ROW_NUMBER() OVER (PARTITION BY userId ORDER BY start_date DESC) AS rn
    FROM plans
    WHERE plan_name IN ('ADVANCED', 'BASIC')
),
switch_records AS (
    SELECT
        curr.userId,
        curr.start_date AS switch_date
    FROM user_plan_order curr
    JOIN user_plan_order prev
        ON curr.userId = prev.userId
        AND curr.rn = prev.rn - 1
    -- 筛选：当前是BASIC，上一个是ADVANCED
    WHERE curr.plan_name = 'BASIC'
      AND prev.plan_name = 'ADVANCED'
)
SELECT
    COUNT(DISTINCT userId) AS num_users,
    DATE_TRUNC('month', switch_date)::DATE AS monthly_changes
FROM switch_records
GROUP BY DATE_TRUNC('month', switch_date)
ORDER BY monthly_changes DESC;

逻辑说明： #

• user_plan_order CTE：给每个用户的套餐按时间倒序编号，最新的套餐排在最前面；
• switch_records CTE：自连接找到用户的连续套餐对，筛选出从ADVANCED到BASIC的切换记录；
• 最后按切换的BASIC套餐所在月份统计用户数，得到预期结果。

内容的提问来源于stack exchange，提问作者Capfer