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

select a.user_id
from (
select a.user_id,
STR_TO_DATE(left(a.log_time,10), '%Y-%m-%d') as log_day,
DATE_ADD(STR_TO_DATE(LEFT(a.log_time,10), '%Y-%m-%d'), INTERVAL -1 DAY) AS log_day1,
DATE_ADD(STR_TO_DATE(LEFT(a.log_time,10), '%Y-%m-%d'), INTERVAL -2 DAY) AS log_day2
from login_tb a) a

inner join register_tb r
on a.user_id = r.user_id

left join login_tb b
on a.user_id = b.user_id
and a.log_day1 = STR_TO_DATE(left(b.log_time,10), '%Y-%m-%d')

left join login_tb c
on a.user_id = c.user_id
and a.log_day2 = STR_TO_DATE(left(c.log_time,10), '%Y-%m-%d')

where b.log_id is not null
and c.log_id is not null

group by 1
order by 1