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

with a as (
select distinct
  user_id,
  date,
  dense_rank() over(partition by user_id order by date) as date_rank
from 
  login
),
b as (
    select a.user_id,a.date as date1 from a
    where date_rank=1
),
c as (
    select a.user_id,a.date as date2 from a
    where date_rank=2
)
select 
  round(count(date2)/count(date1),3) as p
from 
  b 
left join 
  c 
on 
  b.user_id=c.user_id and date_add(date1,interval 1 day)=date2