题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
# 注意,这一题是新用户的次日留存率 并且是计算每一天 # 首先是考虑 新用户 (新用户可以通过第一次登陆的时间来判断) 然后是考虑 留存率 with t1 as ( select distinct user_id, date, first_value(date) over (partition by user_id order by date asc rows between unbounded preceding and unbounded following) as first_date, lead(date, 1, 0) over (partition by user_id order by date asc) as next_date from login ) select date, ifnull(round(sum(if(date=first_date and date_sub(next_date, interval 1 day)=date, 1, 0)) / sum(if(date=first_date, 1, 0)), 3), 0) as p # 计算出来新用户的数量,然后计算新用户中次日登录的数量,进行相除,如果分母为0,则填充为0 from t1 group by date order by date asc;