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

with tmp_data as (
-- 第一步，求各用户，登录的各时间
select a.user_id,date(log_time) as log_time
from login_tb a
inner join register_tb b on a.user_id=b.user_id
)
,tmp_data2 as (
-- 第二步，计算每个用户连续登录天数的排序
select user_id,log_time,row_number()over(partition by log_time order by log_time) as rn
from tmp_data
)
,tmp_data3 as (
-- 第三步，计算每个用户连续登录天数
select user_id,log_time,date_sub(log_time,INTERVAL rn day) as sub_date
from tmp_data2
)
select user_id -- ,count(sub_date) as cs
from tmp_data3
group by user_id -- ,sub_date
having count(1)>2
order by user_id asc

还得是我这种正规军