SQL新用户留存分析

表&＃xff1a;

用户登录日期区间表&＃xff1a;dws.app_user_act_rng&＃xff08;2020-09-04&＃xff09;前一天

其中9999-12-31代表计算当天也登录过&＃xff1b;

 用户日活表&＃xff1a;dws.app_trf_agr_user&＃xff08;2020-09-05&＃xff09;新一天

需求&＃xff1a;

求出用户留存分析&＃xff1a;

留存用户定义&＃xff1a;新用户登录后&＃xff0c;第n日只要登录平台后&＃xff0c;就叫n日留存。

 实现

步骤1&＃xff1a;将当天的用户日活表&＃xff08;2020-09-05&＃xff09;和上一天的用户登录日期区间&＃xff08;2020-09-04&＃xff09;表合并得到当天的用户登录日期区间表&＃xff08;2020-09-05&＃xff09;

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

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

SELECTnvl(a.guid,b.guid) as guid,nvl(a.first_dt,b.dt) as first_dt,nvl(a.rng_start,b.dt) as rng_start,casewhen a.rng_end &＃61; &＃39;9999-12-31&＃39; and b.guid is null then a.dtwhen a.rng_end is null then &＃39;9999-12-31&＃39;else a.rng_endend as rng_end
FROM dws.app_user_act_rng aFULL JOINdws.app_trf_agr_user bON a.dt&＃61;&＃39;2020-09-04&＃39; AND b.dt&＃61;&＃39;2020-09-05&＃39; AND a.guid &＃61; b.guid

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

SELECTa.guid as guid,a.first_dt as first_dt,b.dt as rng_start,&＃39;9999-12-31&＃39; as rng_end
FROM(SELECTguid,first_dtFROM dws.app_user_act_rng WHERE dt&＃61;&＃39;2020-09-04&＃39;GROUP BY guid,first_dtHAVING max(rng_end)!&＃61;&＃39;9999-12-31&＃39;) aJOINdws.app_trf_agr_user bON a.guid&＃61;b.guid and b.dt&＃61;&＃39;2020-09-05&＃39;

3&＃xff0c;part1 UNION ALL part2得到最终结果&＃xff0c;并插入到用户登录日期区间表的新分区&＃xff08;2020-09-05&＃xff09;

步骤2&＃xff1a;求出每个首登日期到几天的第n天&＃xff0c;和第n天留存率

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

每次算出计算当天和首登日期的差值&＃xff08;n&＃xff09;&＃xff0c;和相比于首登的n日留存人数