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

select 
a.date
,round(ifnull(b.first_next_num/a.first_num, 0), 3) p #当天首次登录并且次日也登录的人数/首次登录的人数即为当天留存率
from ( # 新用户表
        select distinct date # 该查询主要目的是：找到每个日期的新用户数
        ,sum(case when (user_id, date) in (select user_id, min(date) from login group by 1) then 1 else 0 end) first_num # 首次登录日期总人数
        from login l
        group by 1
     ) a
left join ( # 次日留存用户表
                select count(user_id) first_next_num # 该查询主要目的是：找到首次登录并且次日又登录的用户数，也就是次日留存用户数
                ,date_add(date, interval -1 day) date2 # 首次登录日期
                from login
                where (user_id, date) in ( # 找到首次登录同时次日也登录的用户id和次日登录日期
                                            select
                                            user_id
                                            ,date_add(min(date), interval 1 day)
                                            from login
                                            group by 1
                                        )
                group by date2
            ) b
on a.date = b.date2
order by a.date;