select *
from sz.game_cn_login;

select *
from sz.game_cn_team;

-- 第一步：先把每个注册用户从注册日开始，后续每天的登录日期取出来
select a.server_role, a.create_time, b.login_time
from sz.game_cn_team a
         left join sz.game_cn_login b on a.server_role = b.server_role
where a.create_time > timestamp '2022-01-01'
  and b.part_month >= '202201'
order by a.server_role, a.create_time limit 1000
-- 第二步：计算登录日期-注册日期（登录日期差）
select a.server_role, a.create_time, date_diff('day', a.create_time, b.login_time) days
from sz.game_cn_team a
         left join sz.game_cn_login b on a.server_role = b.server_role
where a.create_time > timestamp '2022-01-01'
  and b.part_month >= '202201'


-- 第三步：计算某一天新增用户在第N天登录过的人数
select date (a.create_time),
    count (case when date_diff('day', a.create_time, b.login_time) = 1 then b.server_role else null end) cnt2,
    count (case when date_diff('day', a.create_time, b.login_time) = 2 then b.server_role else null end) cnt3,
    count (case when date_diff('day', a.create_time, b.login_time) = 3 then b.server_role else null end) cnt4,
    count (case when date_diff('day', a.create_time, b.login_time) = 4 then b.server_role else null end) cnt5,
    count (case when date_diff('day', a.create_time, b.login_time) = 5 then b.server_role else null end) cnt6,
    count (case when date_diff('day', a.create_time, b.login_time) = 6 then b.server_role else null end) cnt7,
    count (case when date_diff('day', a.create_time, b.login_time) = 13 then b.server_role else null end) cnt14,
    count (case when date_diff('day', a.create_time, b.login_time) = 29 then b.server_role else null end) cnt30
from sz.game_cn_team a
    left join sz.game_cn_login b
on a.server_role = b.server_role
where a.create_time
    > timestamp '2022-01-01'
  and b.part_month >= '202201'
group by 1

-- 第四步：计算留存率
SELECT reg.register_date,
       cnt1,
       cast(cnt2 as double) / cast(cnt1 as double)  as "次留",
       cast(cnt3 as double) / cast(cnt1 as double)  as "3留",
       cast(cnt4 as double) / cast(cnt1 as double)  as "4留",
       cast(cnt5 as double) / cast(cnt1 as double)  as "5留",
       cast(cnt6 as double) / cast(cnt1 as double)  as "6留",
       cast(cnt7 as double) / cast(cnt1 as double)  as "7留",
       cast(cnt14 as double) / cast(cnt1 as double) as "14留",
       cast(cnt30 as double) / cast(cnt1 as double) as "30留"
FROM (
    SELECT date (create_time) register_date,
    COUNT(DISTINCT server_role) cnt1 FROM sz.game_cn_team
    where create_time > timestamp '2022-01-01'
    GROUP BY 1
    order by 1
) reg
LEFT JOIN (
    SELECT date (a.create_time) register_date,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 1 THEN b.server_role ELSE NULL END) cnt2,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 2 THEN b.server_role ELSE NULL END) cnt3,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 3 THEN b.server_role ELSE NULL END) cnt4,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 4 THEN b.server_role ELSE NULL END) cnt5,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 5 THEN b.server_role ELSE NULL END) cnt6,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 6 THEN b.server_role ELSE NULL END) cnt7,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 13 THEN b.server_role ELSE NULL END) cnt14,
        COUNT (CASE WHEN date_diff('day', a.create_time, b.login_time) = 29 THEN b.server_role ELSE NULL END) cnt30
    FROM sz.game_cn_team a
    LEFT JOIN sz.game_cn_login b ON a.server_role = b.server_role
    where a.create_time > timestamp '2022-01-01' and b.part_month >= '202201'
    GROUP BY 1
) log
on reg.register_date = log.register_date;