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

开窗函数解法

思路：

1、增加用户第二次登录日期和登录日期表序号；

2、求当前日期新客和旧客，旧客日期比新客日期大一天既是次日登录客户数，但是旧客数是按照旧客日期求的；

3、次日留存率 = 后一天的旧客数 / 当前日期的新客数。

用到的函数：rank ,lag ,case ,date_add ,round ,ifnull , lead

select 
t2.date
,round(ifnull(lead(t2.o_nub) over(order by t2.date)/n_nub,0),3)
from (
    select 
    t1.date
    ,sum(case when rk = 1 then 1 else 0 end) n_nub -- 新客数
    ,sum(case when t1.date = date_add(t1.o_date,interval 1 day) then 1 else 0 end) o_nub -- 旧客户数
    from (
        select 
        distinct user_id
        ,date
        ,lag(date,1) over(partition by user_id order by date) o_date -- 提前用户第2次登录时间，如果有重复登录，得在原表进行去重
  
        ,rank() over(partition by user_id order by date) rk -- 生成客户登录日期排名
        from login
        ) t1
    where rk = 1 or rk = 2 -- **必须筛选出第1天和第2天，不然第3天会重复计算<!_!>
    group by t1.date) t2