SQL新用户留存分析

表：

用户登录日期区间表：dws.app_user_act_rng（2020-09-04）前一天

其中9999-12-31代表计算当天也登录过；

 

 用户日活表：dws.app_trf_agr_user（2020-09-05）新一天

需求：

求出用户留存分析：

留存用户定义：新用户登录后，第n日只要登录平台后，就叫n日留存。

 

 实现

步骤1：将当天的用户日活表（2020-09-05）和上一天的用户登录日期区间（2020-09-04）表合并得到当天的用户登录日期区间表（2020-09-05）

合并时考虑以下情况：
情况1：昨日不在，今不在（保留原纪录）
情况2：昨日在，今不在（修改此人的最后区间为昨日日期）
情况3：昨日在，今在（保留原纪录）
情况4：新用户（添加记录）
情况5：昨日不在，今在，（老记录要保留，新添一行，需要单独join）

SQL：
1. 用区间记录表的T-1日  FULL JOIN 日活T日
得到结果part1：情况1-4

SELECT
    nvl(a.guid,b.guid) as guid,
    nvl(a.first_dt,b.dt) as first_dt,
    nvl(a.rng_start,b.dt) as rng_start,
    case
        when a.rng_end = '9999-12-31' and b.guid is null then a.dt
        when a.rng_end is null then '9999-12-31'
        else a.rng_end
        end as rng_end
FROM dws.app_user_act_rng a
       FULL JOIN
    dws.app_trf_agr_user b
    ON a.dt='2020-09-04' AND b.dt='2020-09-05' AND a.guid = b.guid

2. 从区间记录表的T-1日中过滤出所有昨天没活跃的人， JOIN T日的日
得到结果part2：情况5

SELECT
    a.guid        as guid,
    a.first_dt    as first_dt,
    b.dt          as rng_start,
    '9999-12-31'   as rng_end
FROM
    (
        SELECT
            guid,
            first_dt
        FROM dws.app_user_act_rng WHERE dt='2020-09-04'
        GROUP BY guid,first_dt
        HAVING max(rng_end)!='9999-12-31'
    ) a
        JOIN
    dws.app_trf_agr_user b
    ON a.guid=b.guid and b.dt='2020-09-05'

3，part1 UNION ALL part2得到最终结果，并插入到用户登录日期区间表的新分区（2020-09-05）

 

步骤2：求出每个首登日期到几天的第n天，和第n天留存率

SELECT
    first_dt      as new_dt,
    datediff('2020-09-05',first_dt) as ret_days, --第n日
    count(if(rng_end='9999-12-31',1,null)) as ret_amt   --第n日留存人数
FROM dws.app_user_act_rng
WHERE dt='2020-09-05' AND datediff('2020-09-05',first_dt)<=30
GROUP BY first_dt;

每次算出计算当天和首登日期的差值（n），和相比于首登的n日留存人数