WITH user_monthly_purchase AS (
    -- Step 1: 提取每个用户有消费的月份，并去重（每月保留一条记录，统一格式化为当月1号）
    SELECT DISTINCT
        user_id,
        DATE_FORMAT(order_date, '%Y-%m-01') AS purchase_month
    FROM user_orders
),
consecutive_check AS (
    -- Step 2: 使用窗口函数 LEAD 获取当前月份往后的第 1 个月和第 2 个月的消费记录
    SELECT
        user_id,
        purchase_month,
        LEAD(purchase_month, 1) OVER (PARTITION BY user_id ORDER BY purchase_month) AS next_month,
        LEAD(purchase_month, 2) OVER (PARTITION BY user_id ORDER BY purchase_month) AS next_next_month
    FROM user_monthly_purchase
),
loyal_users AS (
    -- Step 3: 筛选出曾经连续 3 个月消费的用户
    SELECT DISTINCT user_id
    FROM consecutive_check
    WHERE DATE_ADD(CAST(purchase_month AS DATE), INTERVAL 1 MONTH) = CAST(next_month AS DATE)
      AND DATE_ADD(CAST(purchase_month AS DATE), INTERVAL 2 MONTH) = CAST(next_next_month AS DATE)
),
active_users_30d AS (
    -- Step 4: 找出最近 30 天内（2023-10-02 至 2023-11-01）有消费或有登录的用户
    SELECT DISTINCT user_id
    FROM user_orders
    WHERE order_date >= DATE_SUB('2023-11-01', INTERVAL 30 DAY)
    
    UNION
    
    SELECT DISTINCT user_id
    FROM user_logins
    WHERE login_date >= DATE_SUB('2023-11-01', INTERVAL 30 DAY)
)
-- Step 5: 计算目标用户占全量用户的比例
SELECT
    CONCAT(
        ROUND(
            COUNT(DISTINCT CASE WHEN lu.user_id IS NOT NULL AND au.user_id IS NULL THEN lu.user_id END) 
            / COUNT(DISTINCT ui.user_id) * 100, 
            2
        ), 
        '%'
    ) AS target_user_ratio
FROM user_info ui
LEFT JOIN loyal_users lu ON ui.user_id = lu.user_id
LEFT JOIN active_users_30d au ON ui.user_id = au.user_id;