题解 | #日活次日留存率和新户次日留存率#

考察内容：left join，date_sub，date_format，case when，min/count，distinct，round

思路：

① 取用户每天的登录记录，并对用户去重（distinct uid）

② 关联前一天（ date_sub(login_date, interval 1 day) ）也登录了的用户，生成一个新字段 dt，表示次日留存

③ 取出每个用户作为新用户的日期 date(min(login_date)) as dt_first

生成一个是否新户字段，逻辑是判断登录日期是否为新户日期

④ 用 date_format 对登录日期处理，筛选22年8月的记录

⑤ 使用 case when ... then ... else ... end 将字段次日留存 dt 的值转化为 1 或 0，表示是否次日留存 is_next_day_left

⑥ 使用 case when ... then ... else ... end 生成一个是否为新用户的字段 is_new_user，判断逻辑是登录日期和新用户的日期是否相等，值为 1 或 0

⑦ 计算日活用户次日留存率：次日有登录的用户数除以当天登录的用户数

⑧ 计算新用户的次日留存率：新用户且次日有登录的用户数除以新用户数

⑨ 按日期分组并排序

select
	login_date,
	round(sum(is_next_day_left) / count(1), 2) as uv_left_rate,
	round(sum(case when is_new_user = 1 then is_next_day_left end) / sum(is_new_user), 2) as new_uv_left_rate
from
(
	select
		distinct a.uid,
		login_date,
		case when dt is null then 0 else 1 end as is_next_day_left,
		case when dt_first = login_date then 1 else 0 end as is_new_user
	from
		user_login_tb as a
	left join
	(
			select
				uid,
				date_sub(login_date, interval 1 day) as dt
			from
				user_login_tb
	) as b 
	on a.uid = b.uid and a.login_date = b.dt
	left join
	(
		select
			uid,
			date(min(login_date)) as dt_first
		from
			user_login_tb
		group by
			uid
	) as c 
	on a.uid = c.uid and a.login_date = c.dt_first
	where
		date_format(login_date, "%Y%m") = "202208"
) as t
group by
  login_date
order by
  login_date
;