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,所以用户留存了