题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
会笛卡尔乘积
select t1.day1, ifnull(round(t1.incre_user / (t1.new_user /2 ),3),0.00) p from ( select sum(case when a.tmp=1 then 1 else 0 end ) new_user, count(distinct case when b.day2 - a.day1 = 1 then b.user_id end ) incre_user, a.day1 from (select user_id,date day1 ,row_number() over (partition by user_id order by date ) tmp from login) a join (select user_id,date day2 from login) b on a.user_id = b.user_id group by a.day1 )t1;