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

select d.date,(case when retent_ratio is null then 0.000 else round(retent_ratio,3) end) as retent_ratio_final
from(select c.date, count(c.flag_add) as num_act,count(distinct b.user_id) as num_retent, count(distinct b.user_id)/count(c.flag_add) as retent_ratio
from (select a.user_id,a.date,(case when SUM(b.flag) != 1 then null else 1 end) as flag_add
from (
    select id,user_id,login.date,(case when user_id is not null then 1 end) as flag
    from login) AS a,(
    select id,user_id,login.date,(case when user_id is not null then 1 end) as flag
    from login) AS b
where a.user_id = b.user_id
and a.id>=b.id group by a.id,a.user_id) as c
left join login b
on c.user_id = b.user_id and c.flag_add is not null
and datediff(b.date,c.date) = 1 
group by c.date) as d