Interview: Continuous login questions

Create the data

drop table if exists tmp.luoxiaoxin1; CREATE table if not exists tmp.luoxiaoxin1 as select 10001 as id,'2021-07-01' as `date` union all select 10001 as id,'2021-07-02' as `date`union allselect 10001 as id,'2021-07-02' as `date` union all select 10001 as id,'2021-07-04' as  `date`union allselect 10002 as id,'2021-07-01' as `date` union all select 10002 as id,'2021-07-02' as `date`union allselect 10002 as id,'2021-07-03' as `date`;Copy the code

Here’s why we used dense_rank() because we ran into an interviewer who gave us a duplicate login data. \

select id
       ,`date`
       ,rn
       ,date_sub(`date`, rn) 
       --,count(*) a
from(select distinct id    
            ,`date`    
            ,dense_rank() over (partition by id order by `date`) as rn   
            from tmp.luoxiaoxin1
) tmp
Copy the code

select id
       --,`date`
       --,rn
      ,date_sub(`date`, rn) ,count(*) a
from(select distinct id    
            ,`date`    
            ,dense_rank() over (partition by id order by `date`) as rn   
from tmp.luoxiaoxin1
) tmp
group by id
        ,date_sub(`date`, rn)having count(*)>=3
Copy the code