题解 | #2021年11月每天新用户的次日留存率#

# 1.找到新用户，首次登陆日期
# 2.首次登陆日期+1 找到是否有匹配的日期
# 3.如果进入时间和离开时间跨天，那就说明两天都活跃了，也是留存的一种
# 4.所以应该将intime，outtime合并成一个字段来找活跃的日期
# 5.每日留存率 = count(首次登陆and次日登陆)/count(首次登陆)
# **谓词上推，在最开始的查询就把数据量减小，日期在最里面的查询去重,
# **没办法上推，因为可能是11月份之前注册的

with temp1 as(
select uid,Date_Format(in_time, '%Y-%m-%d') as log_date
from tb_user_log
union all
select uid,Date_Format(out_time, '%Y-%m-%d') as log_date
from tb_user_log
order by uid,log_date
), 
temp2 as (
    SELECT DISTINCT uid, log_date
    FROM temp1
    # where year(log_date) = '2021' and month(log_date) = '11'
)
,temp3 as
(select uid,
      min(log_date) over(partition by uid order by uid) as log_date,
      datediff(log_date,(min(log_date) over(partition by uid))) as diff
from temp2)
#找到首次登陆日期，和首次登陆日期的日期差

select distinct log_date,
       round(sum(if(diff=1,1,0))/count(distinct uid),2) as uv_left_rate
from temp3
where log_date >= '2021-11-01' and log_date <= '2021-11-30'
group by 1