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

-- 思路：

-- 1.统计出每个用户的登录时间和最早登录时间

-- 2.然后统计2021年11月每天新用户的次日留存率（保留2位小数）

-- 本题次日留存用户定义：用户登录时间与最早登录时间的差值为1，则是次日留存用户，计算逻辑如下：

-- if(datediff(uv_dt, min_in_dt)=1, 1,0)

-- 思路：
--      1.统计出每个用户的登录时间和最早登录时间
--      2.然后统计2021年11月每天新用户的次日留存率（保留2位小数）
-- 本题次日留存用户定义：用户登录时间与最早登录时间的差值为1，则是次日留存用户，计算逻辑如下：
--      if(datediff(uv_dt, min_in_dt)=1, 1,0)
with t as(
select 
    a.uid,
    min_in_dt,
    uv_dt
from (
select
    uid,
    date(in_time) as uv_dt
from tb_user_log
union
select 
    uid,
    date(out_time) as uv_dt
from tb_user_log
) as a 
join (
    select 
        uid,
        min(date(in_time)) as min_in_dt
    from tb_user_log
    group by uid
) as b on a.uid = b.uid
)

select 
    min_in_dt as dt,
    round((sum(if(datediff(uv_dt, min_in_dt)=1, 1,0))/count(distinct uid)), 2) as uv_left_rate
from t 
-- 这里 where过滤条件等价于HAVING DATE_FORMAT(dt, "%Y-%m") = "2021-11"
where date_format(min_in_dt, '%Y-%m') = '2021-11'
group by min_in_dt
order by min_in_dt