题解 | #牛客每个人最近的登录日期(三)#

# # 步骤一，当前日期加一天的date1，与当前日期对比：找出日期一致的user_id
# select user_id, date_add(date, interval +1 day) as date1
# from login
# having (user_id, date1) in (select user_id, date from login)

# 步骤二，算出留存率
select round(count(distinct t1.user_id) / count(distinct t2.user_id), 3) as p
from login as t2
left join (
    # 步骤一，当前日期加一天的date1，与当前日期对比：找出日期一致的user_id
    select user_id, date_add(date, interval +1 day) as date1
    from login
    having (user_id, date1) in (select user_id, date from login)
          ) as t1 using(user_id);