sql-常见面试题汇总

1.连续7天登陆但未下单的用户

select c.user_id,
count(c.cum) as cum_01 
from 
(select  a.user_id,a.time01 - row_number() over(partition by a.user_id order_by a.time01 asc) as cum from 
    (
        select distinct user_id ,to_char(event_time,'yyyymmdd') as time01
        from user_behaviour
        where event_name='login' ) a 
    left join 
    (select distinct user_id ,to_char(order_time,'yyyymmdd') as time02
        from order) b 
    on a.user_id=b.user_id
    and a.time01=b.time02
    where b.time02 is null) c 
group by c.user_id
having count(c.cum) >=7;