with data1 as (
    select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
),
data2 as (
    select 
        T.usr_id, 
        T.login_date as T_date, 
        T_1.login_date as T_1_date 
    from 
        data1 as T 
    left join 
        data1 as T_1 
    on 
        T.usr_id = T_1.usr_id 
        and datediff(T.login_date, T_1.login_date) = -1
)
select 
    T_date as first_login_date, 
    concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate 
from 
    data2 
group by 
    T_date 
order by 
    T_date;

select * from user_login_log limit 5;

WITH user_first_visit AS (
    SELECT 
        usr_id,
        MIN(DATE(login_time)) as first_visit
    FROM 
        user_login_log
    GROUP BY 
        usr_id
),
user_daily_activity AS (
    SELECT DISTINCT
        DATE(login_time) as login_date,
        usr_id
    FROM 
        user_login_log
),
user_last_visit AS (
    SELECT 
        login_date,
        usr_id,
        LAG(login_date, 1) OVER (PARTITION BY usr_id ORDER BY login_date) as last_visit
    FROM 
        user_daily_activity
),
user_categories AS (
    SELECT 
        u.login_date,
        u.usr_id,
        f.first_visit,
        u.last_visit,
        CASE 
            WHEN f.first_visit = u.login_date THEN '新增用户'
            WHEN DATEDIFF(u.login_date, COALESCE(u.last_visit, f.first_visit)) BETWEEN 1 AND 3 THEN '留存用户'
            ELSE '回流用户'
        END as user_category
    FROM 
        user_last_visit u
    LEFT JOIN 
        user_first_visit f ON u.usr_id = f.usr_id
),
cohort_analysis AS (
    SELECT 
        f.first_visit as cohort_date,
        u.login_date,
        u.usr_id,
        u.user_category
    FROM 
        user_categories u
    INNER JOIN 
        user_first_visit f ON u.usr_id = f.usr_id
),
cohort_summary AS (
    SELECT 
        cohort_date,
        login_date,
        COUNT(CASE WHEN user_category = '新增用户' THEN 1 END) AS 新增用户数,
        COUNT(CASE WHEN user_category = '留存用户' THEN 1 END) AS 留存用户数,
        COUNT(CASE WHEN user_category = '回流用户' THEN 1 END) AS 回流用户数,
        COUNT(*) AS 总用户数
    FROM 
        cohort_analysis
    GROUP BY 
        cohort_date, login_date
)
SELECT 
    login_date,
    CONCAT(
        FORMAT(SUM(新增用户数) * 100.0 / SUM(总用户数), 2), ', ',
        FORMAT(SUM(留存用户数) * 100.0 / SUM(总用户数), 2), ', ',
        FORMAT(SUM(回流用户数) * 100.0 / SUM(总用户数), 2)
    ) AS pct
FROM 
    cohort_summary
where login_date >='2024-01-01'
GROUP BY 
    login_date
ORDER BY 
    login_date;