# # 步骤1，t1,查询每个用户的初次登录日期
# select user_id, min(date) as min_date
# from login
# group by user_id

# # 步骤2，t2,查询每个日期的新用户
# select date,
# (case when login.date = t1.min_date then user_id else null end) as new
# from login
# inner join (
#     # 步骤1，查询每个用户的初次登录日期
#     select user_id, min(date) as min_date
#     from login
#     group by user_id) as t1 using(user_id)

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

# 步骤4，查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序
select t2.date,
round(ifnull(sum(case when t2.new = t3.user_id then 1 else 0 end) / count(distinct t2.new),0), 3) as p
from (
    # 步骤2，t2,查询每个日期的新用户
    select date,
    (case when login.date = t1.min_date then user_id else null end) as new
    from login
    inner join (
        # 步骤1，查询每个用户的初次登录日期
        select user_id, min(date) as min_date
        from login
        group by user_id) as t1 using(user_id)) as t2
left join (
    # 步骤3，t3,原日期加一天的date1，对比原日期，找出日期一致的原日期和user_id
    select user_id, date,
    date_add(date, interval +1 day) as date1
    from login
    having (user_id, date1) in (select user_id, date from login)) as t3 using(date)
group by t2.date
order by date;