perms-system-web/corrected_retention_query.sql

WITH 
-- 为每个设备确定主要国家（基于最早的注册活动）
device_primary_country AS (
    SELECT 
        DeviceId,
        RegisterDate,
        min_by(Country, RequestTime) as primary_country
    FROM user_days_ads
    WHERE RegisterDate BETWEEN '2025-08-13' AND '2025-09-11' AND ProductCode != ''
    GROUP BY DeviceId, RegisterDate
),

-- 预先计算每个设备的留存状态，避免重复计算
retention_data AS (
    SELECT
        DeviceId,
        RegisterDate,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 1, 1, 0)) as day1_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 2, 1, 0)) as day2_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 3, 1, 0)) as day3_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 4, 1, 0)) as day4_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 5, 1, 0)) as day5_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 6, 1, 0)) as day6_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 7, 1, 0)) as day7_retained,
        MAX(IF(DATEDIFF(BjRequestDate, RegisterDate) = 8, 1, 0)) as day8_retained
    FROM user_days_ads
    WHERE RegisterDate BETWEEN '2025-09-10' AND '2025-09-10' AND ProductCode != ''
    GROUP BY DeviceId, RegisterDate
),

-- 基于主要国家统计特定国家数据
country_stats AS (
    SELECT
        dpc.RegisterDate as date,
        dpc.primary_country as title,
        CONCAT_WS(',',
            CAST(COUNT(DISTINCT(dpc.DeviceId)) AS STRING),
            CAST(SUM(rd.day1_retained) AS STRING),
            CAST(SUM(rd.day2_retained) AS STRING),
            CAST(SUM(rd.day3_retained) AS STRING),
            CAST(SUM(rd.day4_retained) AS STRING),
            CAST(SUM(rd.day5_retained) AS STRING),
            CAST(SUM(rd.day6_retained) AS STRING),
            CAST(SUM(rd.day7_retained) AS STRING),
            CAST(SUM(rd.day8_retained) AS STRING)
        ) as days_str
    FROM device_primary_country dpc
    LEFT JOIN retention_data rd ON dpc.DeviceId = rd.DeviceId AND dpc.RegisterDate = rd.RegisterDate
    WHERE dpc.RegisterDate BETWEEN '2025-09-10' AND '2025-09-10' 
        AND dpc.primary_country IN ('mm','ir','us')
    GROUP BY dpc.RegisterDate, dpc.primary_country
),

-- 总计数据（每个设备只计算一次）
total_stats AS (
    SELECT
        dpc.RegisterDate as date,
        'total' as title,
        CONCAT_WS(',',
            CAST(COUNT(DISTINCT(dpc.DeviceId)) AS STRING),
            CAST(SUM(rd.day1_retained) AS STRING),
            CAST(SUM(rd.day2_retained) AS STRING),
            CAST(SUM(rd.day3_retained) AS STRING),
            CAST(SUM(rd.day4_retained) AS STRING),
            CAST(SUM(rd.day5_retained) AS STRING),
            CAST(SUM(rd.day6_retained) AS STRING),
            CAST(SUM(rd.day7_retained) AS STRING),
            CAST(SUM(rd.day8_retained) AS STRING)
        ) as days_str
    FROM device_primary_country dpc
    LEFT JOIN retention_data rd ON dpc.DeviceId = rd.DeviceId AND dpc.RegisterDate = rd.RegisterDate
    WHERE dpc.RegisterDate BETWEEN '2025-09-10' AND '2025-09-10'
    GROUP BY dpc.RegisterDate
)

-- 合并国家数据和总计数据
SELECT * FROM country_stats
UNION ALL
SELECT * FROM total_stats
ORDER BY
    date,
    CASE 
        WHEN title = 'total' THEN 1
        ELSE 2
    END,
    title;