题解 | #日活次日留存率和新户次日留存率#
日活次日留存率和新户次日留存率
https://www.nowcoder.com/practice/d761c086777845f78e793341474c8ea6
#比较简单的一种思路了。 with tiaojian as ( select uid, login_date, lead(login_date)over(partition by uid order by login_date) as diff, row_number()over(partition by uid order by login_date) as pm from user_login_tb ) select login_date, round( sum(case when datediff(diff,login_date)=1 then 1 else 0 end )/count(distinct uid),2) as uv_left_rate, round( sum(case when pm=1 and datediff(diff,login_date)=1 then 1 else 0 end )/count(distinct case when pm=1 then uid end),2) as new_uv_left_rate from tiaojian group by login_date