如何使用SQL统计活跃用户自计划启动后的通话次数，并按「每两周至少预约1次通话」规则分类为参与用户与非参与用户

嘿，咱们一步步来解决这个问题——你需要实现两个核心需求：一是验证活跃用户从计划启动日开始，每两周周期内都至少有1次通话预约；二是统计这些用户自计划启动到当前日期的总通话次数。下面是具体的思路和SQL实现：

首先得明确几个关键细节：

• 「两周周期」的计算：从用户的plan_start_date开始，每连续14天为一个周期（比如第1-14天为第一个周期，第15-28天为第二个，以此类推，直到当前日期）
• 「参与用户」的判定：所有周期内的预约次数都≥1（只要有一个周期没预约，就不算参与用户）
• 统计范围：仅包含用户plan_start_date到当前日期的通话记录（如果要统计已完成的通话，还需要过滤call_status或call_done_time）

我们可以用递归CTE来生成每个用户的周期区间，再关联通话表做统计和验证：

WITH RECURSIVE user_plan_cycles AS (
    -- 第一步：获取所有活跃用户的计划起始日，生成第一个两周周期
    SELECT 
        p.user_id,
        p.plan_start_date,
        -- 第一个周期：从计划启动日到启动日+13天（包含当天，共14天）
        DATE_ADD(p.plan_start_date, INTERVAL 13 DAY) AS cycle_end,
        CURRENT_DATE() AS current_date
    FROM payment_table p
    WHERE p.is_active = 1

    UNION ALL

    -- 递归生成后续的两周周期，直到周期结束日超过当前日期
    SELECT 
        user_id,
        DATE_ADD(cycle_end, INTERVAL 1 DAY) AS plan_start_date,
        DATE_ADD(cycle_end, INTERVAL 14 DAY) AS cycle_end,
        current_date
    FROM user_plan_cycles
    WHERE cycle_end < current_date
),
user_cycle_bookings AS (
    -- 第二步：统计每个用户每个周期内的预约次数
    SELECT 
        upc.user_id,
        upc.plan_start_date AS cycle_start,
        upc.cycle_end,
        COUNT(c.id) AS booking_count
    FROM user_plan_cycles upc
    LEFT JOIN call_event_table c 
        ON upc.user_id = c.user_id
        -- 关联条件：预约时间在当前周期内
        AND c.booking_time BETWEEN upc.plan_start_date AND upc.cycle_end
        -- 如果你需要统计已完成的通话，这里可以加过滤：AND c.call_status = 'completed'
    GROUP BY upc.user_id, upc.plan_start_date, upc.cycle_end
),
user_engagement_check AS (
    -- 第三步：验证用户是否每个周期都有预约
    SELECT 
        user_id,
        -- 所有周期的最小预约数≥1，说明每个周期都至少有1次预约
        MIN(booking_count) >= 1 AS is_engaged_user,
        COUNT(DISTINCT cycle_start) AS total_cycles -- 输出总周期数方便核对
    FROM user_cycle_bookings
    GROUP BY user_id
),
user_total_calls AS (
    -- 第四步：统计用户自计划启动到现在的总通话次数
    SELECT 
        p.user_id,
        COUNT(c.id) AS total_call_count
    FROM payment_table p
    LEFT JOIN call_event_table c 
        ON p.user_id = c.user_id
        AND c.call_done_time BETWEEN p.plan_start_date AND CURRENT_DATE()
        -- 同样，如需过滤已完成通话，添加：AND c.call_status = 'completed'
    WHERE p.is_active = 1
    GROUP BY p.user_id
)
-- 最终合并所有结果
SELECT 
    utc.user_id,
    utc.total_call_count,
    uec.is_engaged_user,
    uec.total_cycles,
    p.plan_start_date,
    p.plan_name
FROM user_total_calls utc
JOIN user_engagement_check uec ON utc.user_id = uec.user_id
JOIN payment_table p ON utc.user_id = p.user_id AND p.is_active = 1
ORDER BY utc.user_id;

1. 递归周期生成：用WITH RECURSIVE为每个活跃用户生成专属的两周周期，避免了统一周期的误差（因为每个用户的计划启动时间不同）
2. 预约验证逻辑：通过MIN(booking_count) >=1来判断，只要有一个周期的预约数为0，这个用户就不符合参与用户的定义
3. 时间范围过滤：统计总通话次数时，严格以用户的plan_start_date为起始点，而不是固定日期，保证数据准确性
4. 灵活调整：代码里加了注释，如果你需要统计「已完成通话」而非「预约」，只需调整关联条件和过滤规则即可

• 时区一致性：确保CURRENT_DATE()、plan_start_date、booking_time等时间字段的时区一致，避免统计偏差
• 多活跃计划：如果一个用户有多个活跃计划，需要考虑是否按用户合并统计，还是按单个计划处理（当前代码默认一个用户只有一个活跃计划）

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