题解 | #牛客每个人最近的登录日期(三)#
牛客每个人最近的登录日期(三)
https://www.nowcoder.com/practice/16d41af206cd4066a06a3a0aa585ad3d
解题步骤:
1.用户的登录行为按日期排列好,加一列显示第二次登录的日期。
select user_id r_user ,`date` ,lead(`date`) over(partition by user_id order by `date` ) 2ndlogday from login2.数出第二日留存的用户数量
select count(distinct r_user) from ( select user_id r_user ,`date` ,lead(`date`) over(partition by user_id order by `date` ) 2ndlogday from login ) reuser where 2ndlogday=date_add(`date`,interval 1 day)3.数出总的用户数
select count(distinct user_id) from login)4.相除,取三位小数,注意最后一步需要写select,但不用写from
select round((select count(distinct r_user) from ( select user_id r_user ,`date` ,lead(`date`) over(partition by user_id order by `date` ) 2ndlogday from login ) reuser where 2ndlogday=date_add(`date`,interval 1 day)) / (select count(distinct user_id) from login),3) p