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

# 将进入和离开时间格式化，然后像上一题那样提取组合，再去重，因为只涉及两天跨天的情况，所以这样就能得到每个用户的活跃日期
# 然后在每个用户维度使用lag/lead函数判断有没有在第二天又活跃
# 根据时间排序。新增一列是每个用户最开始活跃日期
# 如果开始日期和下一个日期都存在，并且开始日期等于首次活跃日期，那就说明新用户次日留存了，设为1，否则为0，还有再设一列是新用户登录记录
# 两列聚合后相除

# 创建临时表
WITH temp_0 AS(
	# 查询uid，开始日期，
	# 使用LEAD开窗函数针对每个用户分页，使用日期正序，得到开始日期的下一个日期，
	# 使用MIN开窗聚合函数，同样的分页和排序，得到每个用户的初始活跃日期
    SELECT uid, act_date, LEAD(act_date, 1) OVER(PARTITION BY uid ORDER BY act_date) next_date, MIN(act_date) OVER(PARTITION BY uid ORDER BY act_date) uf_date
    FROM( # 子查询，查询uid，格式化的开始日期和离开日期组合并集（自动去重）
		# 这里不能用上一题的文章id筛选，因为只要有记录那用户一定登录活跃了
        SELECT uid,	DATE_FORMAT(in_time, '%Y-%m-%d') act_date
        FROM tb_user_log
        UNION # 自动去重
        SELECT uid,	DATE_FORMAT(out_time, '%Y-%m-%d') act_date
        FROM tb_user_log
        ) a # 每个子查询作为表必须要设置别名
    # 不能加在这，因为有用户十月份就进来。加这的话就意味着系统十一月才上线
    # WHERE act_date BETWEEN '2021-11-01' AND '2021-11-30'
    )

SELECT act_date, ROUND(SUM(su_c)/SUM(uf_c), 2) uv_left_rate
FROM( # 子查询，查询uid，开始日期
		# 判断是否次日成功留存，是就是1，不是就是0
		# 判断是否是当日新增活跃的，1,0
    SELECT uid, act_date, 
            CASE WHEN act_date IS NOT NULL AND next_date  IS NOT NULL AND act_date = uf_date THEN 1 ELSE 0 END su_c,
            CASE WHEN act_date IS NOT NULL AND act_date = uf_date THEN 1 ELSE 0 END uf_c
    FROM temp_0
    ) a # 别名是局部变量，实际似乎也没用到，跟上面取一样也行
# 限制日期
WHERE act_date BETWEEN '2021-11-01' AND '2021-11-30'
GROUP BY act_date
# 限制一天是否有新增活跃
HAVING SUM(uf_c) != 0
ORDER BY act_date

踩的坑：没有设置好在哪限制日期，应该在最后，理由见21行