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

# 使用窗口函数min()over和lead()over()
select distinct user_id, `date`, min(`date`)over(partition by user_id) first_day,
lead(`date`, 1)over(partition by user_id order by `date`) sec_day
from login l;

# 将用户第一次登录的记录从使用了窗口函数的查询结果表中提取出来并存储为临时表
with t1 as(
    select * from (
        select distinct user_id, `date`, min(`date`)over(partition by user_id) first_day,
        lead(`date`, 1)over(partition by user_id order by `date`) sec_day
        from login l
    ) k1
    where `date` = first_day
)

# 获取包括无新用户的日期的次日留存率列表
select q1.`date`, ifnull(p,0.000) p
from (select distinct `date` from login) q1
left join (
    # 查询出有新用户的每个日期的新用户次日留存率
    select `date`, round(sum(if(datediff(sec_day, first_day)=1,1,0))/count(*),3) p
    from t1
    group by `date`
) q2
on q1.`date` = q2.`date`
order by q1.`date`;