题解 | #查询连续登陆的用户#

with a as(
select lt.user_id,date(lt.log_time) as log_time
from login_tb as lt
left join register_tb as rt on rt.user_id = lt.user_id
where rt.user_id in (select user_id from register_tb)),
b as(
select user_id,
date_sub(log_time,interval row_number() over(partition by user_id order by log_time) day) as diff
from a),
c as (
select user_id,count(diff) as login_days
from b 
group by user_id,diff)
select user_id from c where login_days>= 3

复杂的条件一层一层写，写在一起SQL可能不认识

with a as(select user_id,date(log_time) log_time
from login_tb
where user_id in (select user_id from register_tb)),
b as(select user_id,log_time,
row_number() over(partition by user_id order by log_time) as diff
from a)
select distinct user_id from b where diff = 3