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

问题：每日新用户的次日留存率。
解题思路：1、利用开窗函数row_number() 当r=1时即该行用户为当日新用户；
函数lag（）将日期上移，得到l_date，当date-l_date的差为-1时，说明l_date是date的次日，这里要的只是次日即第二次登陆，所以同时r=2即可得出l_date对应次日有留存的用户数
2、分别利用条件聚合函数sum(case when 条件1 ... then 1 else 0 end)求出该日的新用户数和对应次日的留存数
3、因为要有每天的日期，而留存用户数表中日期不全，故用新用户数表left join留存用户数表
4、次日留存率=次日留存数/当日新用户数，考虑分母为0 故对公式用ifnull(value,0)

select
d.date,
round(ifnull(d.old/d.new,0),3)
from
(SELECT
c.date,
c.new,
sum(case when a.r=2 and DATEDIFF(a.date,a.l_date)=1 then 1 else 0 end) old
from
(
SELECT
b.date,
sum(case when b.r=1 then 1 else 0 end) new
from
(SELECT
*,
lag(date) over(partition by user_id order by date) l_date,
ROW_NUMBER() over(partition by user_id order by date) r
from login
)b
GROUP by b.date
)c #当日新用户数
left join
(
SELECT
*,
lag(date) over(partition by user_id order by date) l_date,
ROW_NUMBER() over(partition by user_id order by date) r
from login
)a
on c.date=a.l_date
group by 1
)d
group by 1