hive经典面试题2--次日留存、3日、7日留存怎么计算

背景：互联网公司，分析app上线后的效果，通常会通过很多指标来分析，其中一个指标就是留存率，留存率中最重要的就是次日留存，如果次日留存很高，说明这个app很成功，用户粘性高。留存是怎么定义的呢？一个用户第一天来了，第二天又来了，说明这个用户留存了。次日留存率就是100%第一天来了100个用户，第二天这100用户中只有50个用户过来了，第三天这100个用户中只有30个用户过来了；那么次日留存率就是5

不想起的昵称

10881人浏览 · 2021-11-25 18:30:41

不想起的昵称 · 2021-11-25 18:30:41 发布

背景：互联网公司，分析app上线后的效果，通常会通过很多指标来分析，其中一个指标就是留存率，留存率中最重要的就是次日留存，如果次日留存很高，说明这个app很成功，用户粘性高。

留存是怎么定义的呢？

一个用户第一天来了，第二天又来了，说明这个用户留存了。次日留存率就是100%
第一天来了100个用户，第二天这100用户中只有50个用户过来了，第三天这100个用户中只有30个用户过来了；那么次日留存率就是50/100×100=50%，3日留存率就是30/100×100=30%（比较的都是第一天这一批100用户）

我们来看看通过代码怎么计算留存率：

次日留存：

23号用户在24号的留存情况：

select 
  t1.apptypeid,
  count(t1.uid) as active_users, --23号用户数
  count(t2.uid) as day2_active_users, --23号用户在24号的留存数
  concat(count(t2.uid)/count(t1.uid)*100,'%') as day2_active_user_rate, --23号用户在24号的留存率
  t1.dt
from 
(select
  apptypeid,
  uid,
  dt
from ods_center.ods_app_open
where dt='20211123'
group by apptypeid,uid,dt) t1

left join
(select
  apptypeid,
  uid,
  dt
from ods_center.ods_app_open
where dt='20211124'
group by apptypeid,uid,dt) t2
on t1.apptypeid=t2.apptypeid and t1.uid=t2.uid
group by t1.apptypeid,t1.dt;

次日、3日、7日留存：

18号用户在未来7天的留存情况：

select 
  apptypeid,
  active_users, --18号用户数
  day2_active_users, 
  concat(day2_active_users/active_users*100,'%') as day2_active_user_rate, --18号用户数的次日留存
  day3_active_users,
  concat(day3_active_users/active_users*100,'%') as day3_active_user_rate, --18号用户数的3日留存
  day4_active_users,
  concat(day4_active_users/active_users*100,'%') as day4_active_user_rate, --18号用户数的4日留存
  day5_active_users,
  concat(day5_active_users/active_users*100,'%') as day5_active_user_rate, --18号用户数的5日留存
  day6_active_users,
  concat(day6_active_users/active_users*100,'%') as day6_active_user_rate, --18号用户数的6日留存
  day7_active_users,
  concat(day7_active_users/active_users*100,'%') as day7_active_user_rate, --18号用户数的7日留存
  dt
from 
(select 
  t1.apptypeid,
  count(t1.uid) as active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=1 then t2.uid end) as day2_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=2 then t2.uid end) as day3_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=3 then t2.uid end) as day4_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=4 then t2.uid end) as day5_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=5 then t2.uid end) as day6_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=6 then t2.uid end) as day7_active_users,
  t1.dt
from 
(select
  apptypeid,
  uid,
  dt,
  from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt
from ods_center.ods_app_open
where dt='20211118'
group by apptypeid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd')) t1

left join
(select
  apptypeid,
  uid,
  dt,
  from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt
from ods_center.ods_app_open
where dt>'20211118' and dt<='2021124'
group by apptypeid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd')) t2
on t1.apptypeid=t2.apptypeid and t1.uid=t2.uid
group by t1.apptypeid,t1.dt) t3