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

先求出每一个用户记录的下一条登录记录的时间和最早登录时间

SELECT
     DATE_FORMAT(in_time,'%Y-%m-%d') dt,
     DATE_FORMAT(out_time,'%Y-%m-%d') out_time, # 退出登录时间，用于判断登录时间是跨天
     LEAD(in_time,1) OVER(PARTITION BY uid ORDER BY in_time) pre_record,# 下次登录时间
     MIN(in_time) OVER (PARTITION BY uid ORDER BY in_time) min_day# 最早登录时间
FROM
     tb_user_log 

求出每天的新用户和次日留存用户

SELECT
     dt,
     SUM(IF(dt = DATE_FORMAT(min_day,'%Y-%m-%d'),1,0)) new_user, # 新用户
     SUM(IF((dt = DATE_FORMAT(min_day,'%Y-%m-%d') AND 
         DATEDIFF(dt,DATE_FORMAT(pre_record,'%Y-%m-%d')) = -1) OR
          DATEDIFF(dt, out_time) = -1,1,0)) left_user # 次日留存用户，注意登录时间段跨天的情况
 FROM (
     SELECT
         DATE_FORMAT(in_time,'%Y-%m-%d') dt,
                     DATE_FORMAT(out_time,'%Y-%m-%d') out_time,
         LEAD(in_time,1) OVER(PARTITION BY uid ORDER BY in_time) pre_record,
         MIN(in_time) OVER (PARTITION BY uid ORDER BY in_time) min_day
     FROM
         tb_user_log 
 )tmp1
     GROUP BY dt
 HAVING new_user > 0 AND DATE_FORMAT(dt,'%Y-%m') = '2021-11'

计算每天新增用户的次日留存率

SELECT
 dt,
 IF( new_user = 0, 0,ROUND(left_user / new_user,2)) uv_left_rate
FROM(
 SELECT
     dt,
     SUM(IF(dt = DATE_FORMAT(min_day,'%Y-%m-%d'),1,0)) new_user,
     SUM(IF((dt = DATE_FORMAT(min_day,'%Y-%m-%d') AND 
         DATEDIFF(dt,DATE_FORMAT(pre_record,'%Y-%m-%d')) = -1) OR
          DATEDIFF(dt, out_time) = -1,1,0)) left_user
 FROM (
     SELECT
         DATE_FORMAT(in_time,'%Y-%m-%d') dt,
                     DATE_FORMAT(out_time,'%Y-%m-%d') out_time,
         LEAD(in_time,1) OVER(PARTITION BY uid ORDER BY in_time) pre_record,
         MIN(in_time) OVER (PARTITION BY uid ORDER BY in_time) min_day
     FROM
         tb_user_log 
 )tmp1
     GROUP BY dt
 HAVING new_user > 0 AND DATE_FORMAT(dt,'%Y-%m') = '2021-11'
)tmp2
ORDER BY dt ASC;