Hive综合案例练习（中级）第二十八题：查询有新注册用户的当天的新用户数量、新用户的第一天留存率

查询有新注册用户的当天的新用户数量、新用户的第一天留存率

题目需求

从用户登录明细表（user_login_detail）中首次登录算作当天新增，第二天也登录了算作一日留存

结果如下：

first_login（注册时间）	Register（新增用户数）	Retention（留存率）
2021-09-21	1	0.0
2021-09-22	1	0.0
2021-09-23	1	0.0
2021-09-24	1	0.0
2021-09-25	1	0.0
2021-09-26	1	0.0
2021-09-27	1	0.0
2021-10-04	2	0.5
2021-10-06	1	0.0

代码实现

• 获取每个用户首次登录时间和第二天是否登录，并求得每天新增和留存数量

select first_login,
       count(t1.user_id) register,
       count(t2.user_id) remain
from (
         select user_id,
                date_format(min(login_ts), 'yyyy-MM-dd') first_login
         from user_login_detail
         group by user_id
     ) t1
         left join user_login_detail t2
                   on t1.user_id = t2.user_id and
                      datediff(date_format(t2.login_ts, 'yyyy-MM-dd'), date_format(t1.first_login, 'yyyy-MM-dd')) = 1
group by first_login;

• 求新增数量和留存率

select first_login,
       register,
       remain / register rentention
from (
         select first_login,
                count(t1.user_id) register,
                count(t2.user_id) remain
         from (
                  select user_id,
                         date_format(min(login_ts), 'yyyy-MM-dd') first_login
                  from user_login_detail
                  group by user_id
              ) t1
                  left join user_login_detail t2
                            on t1.user_id = t2.user_id and
                               datediff(date_format(t2.login_ts, 'yyyy-MM-dd'),
                                        date_format(t1.first_login, 'yyyy-MM-dd')) = 1
         group by first_login
     ) t2