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

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

• 一个用户第一天来了，第二天又来了，说明这个用户留存了。次日留存率就是100%
• 第一天来了100个用户，第二天这100用户中只有50个用户过来了，第三天这100个用户中只有30个用户过来了；那么次日留存率就是50/100100=50%，3日留存率就是30/100100=30%

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

select 
  t1.apptypeid,
  count(t1.uid) as active_users,
  count(t2.uid) as day2_active_users,
  concat(count(t2.uid)/count(t1.uid)*100,'%') as day2_active_user_rate,
  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;

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

select 
  apptypeid,
  active_users,
  day2_active_users,
  concat(day2_active_users/active_users*100,'%') as day2_active_user_rate,
  day3_active_users,
  concat(day3_active_users/active_users*100,'%') as day3_active_user_rate,
  day4_active_users,
  concat(day4_active_users/active_users*100,'%') as day4_active_user_rate,
  day5_active_users,
  concat(day5_active_users/active_users*100,'%') as day5_active_user_rate,
  day6_active_users,
  concat(day6_active_users/active_users*100,'%') as day6_active_user_rate,
  day7_active_users,
  concat(day7_active_users/active_users*100,'%') as day7_active_user_rate,
  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