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

-- 先把新用户找出来（只包含有新用户的日期）
with new_u as (
select user_id,min(date) as first_day
from login
group by 1
),
-- 如果新用户第二天登录就打个标记为1，否则为0
mark_tab as (
select n.user_id,n.first_day,case when l.date then 1 else 0 end as mark
from new_u n left join login l on n.user_id = l.user_id and l.date = n.first_day + interval 1 day
),
-- 把有新用户的日期的次日留存率算出来
temp as (
select first_day as date,round(sum(mark)/count(*),3) as p
from mark_tab
group by 1
)
-- 左连接总表，取出所有日期（包含没有新用户的日期）
select distinct l.date,ifnull(t.p,0) as p
from login l left join temp t using(date)
order by 1;