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

思路：

1. In_time和out_time都算活跃——分别选取为act_date并用**union（而非union all）**连接
2. 新用户——act_date=latest_date=min(act_date) /min(act_date) over（以uid分类）
3. 次日留存率：

• 次日—利用**lead(act_date,1) over (partition by uid order by date)as date1 **把日期列在各自的uid大块内整体向前移一行
• 次日留存——datediff(date1,act_date)=1
• 次日留存率——avg(if(datediff(date1,act_date)=1,1,0))（以dt分类）

代码：

1. 取act_date表q1

select uid,date(in_time) as act_date
          from tb_user_log
          union 
          select uid,date(out_time) as act_date
          from tb_user_log
order by uid,act_date

2. 用于判断新用户的latest_date和用于判断次日的date1都以uid分类，可放在一表q2中，此时前者宜选用min()over，这样都不涉及**group by **语句

with q1 as
(select uid,date(in_time) as act_date
          from tb_user_log
          union 
          select uid,date(out_time) as act_date
          from tb_user_log
order by uid,act_date
         )
select uid,act_date,lead(act_date,1) over 
     (partition by uid order by act_date) as date1,
     min(act_date) over (partition by uid) as first_date
     from q1 

3. 取次日留存率，注意取2021年11月数据，且group by dt

with q1 as
(select uid,date(in_time) as act_date
          from tb_user_log
          union 
          select uid,date(out_time) as act_date
          from tb_user_log
order by uid,act_date
         )
select act_date as dt,
round(avg(if(datediff(date1,act_date)=1,1,0)),2) as uv_left_rate
from
    (select uid,act_date,lead(act_date,1) over 
     (partition by uid order by act_date) as date1,
     min(act_date) over (partition by uid) as first_date
     from q1 
    ) as q2
where act_date=first_date and year(act_date)=2021 and month(act_date)=11
group by dt
order by dt