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

知识点

1. 窗口函数：lead()
2. case when
3. 时间函数：date_add()

理解题意

一开始没想出来，参考了大佬的解法。才知道自己对于题意的理解错了。这是才是对于留存率正确的理解。然后后续代码我用窗口函数实现。

1. 计算日活用户次日留存率：次日有登录的用户数除以当天登录的用户数
2. 计算新用户的次日留存率：新用户且次日有登录的用户数除以新用户数

难点在于求新用户的次日留存率，关键代码：

sum(case when is_new_user = 1 then is_next_date_left else null end) / sum(is_new_user),2)

我们只需要看 新用户的次日留存情况，其他的不看，所以是新用户的话，即返回它的次日留存情况这个字段is_next_date_left

然后 除以 当天所有的新用户的数量sum(is_new_user)

分步解题

秒就秒在新增了两列

select *,
case when lead(login_date,1) over(partition by uid order by login_date) =  date_add(login_date, interval 1 day) then 1 else 0 end as is_next_date_left,
case when login_date = min(login_date) over(partition by uid order by login_date) then 1 else 0 end as is_new_user
from user_login_tb

select login_date,
round(sum(is_next_date_left)/count(1),2) as uv_left_rate,
round(sum(case when is_new_user = 1 then is_next_date_left else null end) / sum(is_new_user),2) as new_uv_left_rate
from tb # 上面那个表
group by login_date
order by login_date

完整代码

select login_date,
round(sum(is_next_date_left)/count(1),2) as uv_left_rate,
round(sum(case when is_new_user = 1 then is_next_date_left else null end) / sum(is_new_user),2) as new_uv_left_rate
from
(select *,
case when lead(login_date,1) over(partition by uid order by login_date) =  date_add(login_date, interval 1 day) then 1 else 0 end as is_next_date_left,
case when login_date = min(login_date) over(partition by uid order by login_date) then 1 else 0 end as is_new_user
from user_login_tb) as tb
group by login_date
order by login_date