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

/*
请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:
*/
select date,
       ifnull(round(count(case when lc=1 and ranking=1 then 1 else null end)/count(case when ranking=1 then 1 else null end),3),0)
from 
(select user_id,date,
datediff(lead(date)over(partition by user_id order by date),date) lc,
dense_rank()over(partition by user_id order by date) ranking
from login)data1
group by date
order by date