题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
select
date,
round(if(sum(new_user) != 0,
sum(
case
when new_user=1 and lc=1 then 1
else 0 end
)/sum(new_user), 0), 3)
from(
select
*,
if (date = min(date) over (partition by user_id), 1, 0) as new_user,
if (datediff(Lead(date, 1)over(partition by user_id order by date), date) = 1, 1, 0) as lc
from
login
) t1
group by date
