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

sql script

-- new user every day numbers
select date, count(user_id) as new_number
from (select user_id, min(date) as date
      from login
      group by user_id) tmp
group by date




-- second day user login
select user_id, date
from login
where (user_id, date)
in (select user_id, date_add(min(date), interval 1 day) as date
    from login
    group by user_id)



-- first second day login user_id and first_day
select date, count(user_id) as second_number
from (select user_id, min(date) as date
      from login
      where user_id in (select user_id
                        from login
                        where (user_id, date)
                        in (select user_id, date_add(min(date), interval 1 day) as date
                            from login
                            group by user_id))
      group by user_id) tmp
group by date



-- exists day rate
select new.date, round(second.second_number/new.new_number,3)
from (select date, count(user_id) as new_number
      from (select user_id, min(date) as date
            from login
            group by user_id) tmp
      group by date) as new,
      (select date, count(user_id) as second_number
       from (select user_id, min(date) as date
             from login
             where user_id in (select user_id
                               from login
                               where (user_id, date)
                               in (select user_id, date_add(min(date), interval 1 day) as date
                                   from login
                                   group by user_id))
             group by user_id) tmp
       group by date) as second
where new.date=second.date



-- result
select l.date, coalesce(tmp_day.p, 0.000)
from (select distinct date from login) l
left join (select new.date as date, round(second.second_number/new.new_number,3) as p
           from (select date, count(user_id) as new_number
                 from (select user_id, min(date) as date
                       from login
                       group by user_id) tmp
                 group by date) as new,
           (select date, count(user_id) as second_number
            from (select user_id, min(date) as date
                  from login
                  where user_id in (select user_id
                                    from login
                                    where (user_id, date)
                                    in (select user_id, date_add(min(date), interval 1 day) as date
                                        from login
                                        group by user_id))
                  group by user_id) tmp
            group by date) as second
           where new.date=second.date) tmp_day on l.date=tmp_day.date