sql面试题

1、连续3天登录的用户

select 
    user_id 
    ,count(1) as `连续登录天数`
    ,min(login_date) as `开始日期`
    ,max(login_date) as `结束日期`
from (
        select user_id
              ,login_date
              ,rn
             ,date_sub(login_date,Interval rn DAY ) AS subed_date
        from (
             select user_id 
             ,login_date
             ,row_number() over(partition by user_id order by login_date ) as rn
             from user_login
        )t
)tt
group by user_id ,subed_date
having `连续登录天数` >3 ;