牛客刷题sql 求留存率问题

select date
,ifnull(round((sum(case when (user_id,date)in
(select user_id,date_add(date,interval -1 day)
from login) and (user_id,date)in (select user_id,min(date)from login group by user_id)
then 1 else 0 end))/
(sum(case when (user_id,date)in
(select user_id,min(date)from login group by user_id)
then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

select date
,ifnull(round((sum(case when (user_id,date_add(date,interval 1 day))in
(select user_id,date
from login) and (user_id,date)in (select user_id,min(date)from login group by user_id)
then 1 else 0 end))/
(sum(case when (user_id,date)in
(select user_id,min(date)from login group by user_id)
then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

很厉害的答案！ 上面问到的为什么是-1不是+1： select user_id,date_add(date,interval -1 day) from login 表示用户登录时间的前一天， case when (user_id,date)in (select user_id,date_add(date,interval -1 day) from login) 则表示存在一个登录时间的前一天是当前日期，也就是说当前日期的第二天用户也登录了。比如user_id 为 1 的用户2021-01-02和2021-01-03都登录了，那么select user_id,date_add(date,interval -1 day)会返回 2021-01-01和2021-01-02， 因为2021-01-02在里面，且2021-01-02是用户的min_date，所以用户留存了