with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
(
select
user_id
,date_format(start_time,'%Y-%m-01') mon
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_num + t3.loc_num = 0
group by 1,2
having count(distinct date(start_time))>=5
)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1;

with start_end_loc_map as (
select 
id
,user_id
,start_time
,end_time
,start_loc
,end_loc
from hello_bike_riding_rcd
where (start_loc in (select loc_nm from gd_loc_map where loc_type = '写字楼') and end_loc in (select loc_nm from gd_loc_map where loc_type = '地铁站')) 
or (start_loc in (select loc_nm from gd_loc_map where loc_type = '地铁站') and end_loc in (select loc_nm from gd_loc_map where loc_type = '写字楼')) 
),
start_end_time as (
select
id
,user_id
,date_format(start_time,'%Y%m') as start_time
,date_format(end_time,'%Y%m') as end_time
,start_loc
,end_loc
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time
,count(start_time) as riding_times_count
from start_end_time
group by
user_id
,start_time
having count(start_time) >= 5
order by
user_id
,start_time
),
continous_record as (
select
user_id
,start_time
,lag(start_time,1) over (partition by user_id order by start_time ) as previous_one_record
,lag(start_time,2) over (partition by user_id order by start_time ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct user_id
 ,period_diff (start_time, previous_one_record) as monthdifference1
 ,period_diff (start_time, previous_two_record) as monthdifference2
from continous_record
where period_diff (start_time, previous_one_record) = 1 and period_diff (start_time, previous_two_record) = 2
)
select
distinct user_id
,case when hello_bike_riding_rcd.user_id in (select user_id from active_customer) then 1
else 0
 end as active_tag
from hello_bike_riding_rcd
order by user_id;