大数据技术之高频面试题（七）：用户行为数据分析（下篇）

问题导读：
1、如何设计用户留存率表？
2、什么是沉默用户，如何进行表的创建？
3、如何设计本周回流用户数？
4、如何设计流失用户数？
5、如何设计活跃用户数？


上一篇：大数据技术之高频面试题（七）：用户行为数据分析(上篇)

5.8 需求三：用户留存主题



5.8.1 DWS层日留存明细表


5.8.2 ADS层留存用户数表


5.8.3 ADS层留存用户率表


5.9 需求四：沉默用户


5.10 需求五：本周回流用户数


5.11 需求六：流失用户数


5.12 需求七：最近连续3周活跃用户数


5.13 需求八：最近七天内连续三天活跃用户数


5.14 需求逻辑
5.14.1 如何分析用户活跃？
在启动日志中统计不同设备id出现次数。

5.14.2 如何分析用户新增？
用活跃用户表 left join 用户新增表，用户新增表中mid为空的即为用户新增。

5.14.3 如何分析用户1天留存？
留存用户=前一天新增 join 今天活跃
用户留存率=留存用户/前一天新增

5.14.4 如何分析沉默用户？
(登录时间为7天前,且只出现过一次)
按照设备id对日活表分组，登录次数为1，且是在一周前登录。

5.14.5 如何分析本周回流用户？
本周活跃left join本周新增 left join上周活跃，且本周新增id和上周活跃id都为null

5.14.6 如何分析流失用户？
(登录时间为7天前)
按照设备id对日活表分组，且七天内没有登录过。

5.14.7 如何分析最近连续3周活跃用户数？
按照设备id对周活进行分组，统计次数大于3次。

5.14.8 如何分析最近七天内连续三天活跃用户数？
1）查询出最近7天的活跃用户，并对用户活跃日期进行排名
2）计算用户活跃日期及排名之间的差值
3）对同用户及差值分组，统计差值个数
4）将差值相同个数大于等于3的数据取出，然后去重(去的是什么重???)，即为连续3天及以上活跃的用户