with map as (
	select loc_nm, if(loc_nm = '将台西', '地铁站', loc_type) loc_type from gd_loc_map a
)
 , ride as (
	select a.*
		 , b.loc_type as start_loc_type
		 , c.loc_type as end_loc_type
		 , if((b.loc_type = '写字楼' and c.loc_type = '地铁站') or (c.loc_type = '写字楼' and b.loc_type = '地铁站'), 1,
		0) is_active_ride
		 , date_format(start_time, '%Y-%m') start_month
	from hello_bike_riding_rcd a
	left join map b on a.start_loc = b.loc_nm
	left join map c on a.end_loc = c.loc_nm
)
 , user as (
	select distinct user_id
	from ride
)
 , active as (
	select start_month, user_id, sum(active_ride_num) active_ride_num
	from (
		select start_month, user_id, sum(is_active_ride) active_ride_num
		from ride a
		group by 1, 2
		union all
		select start_month, user_id, 0
		from (
			select distinct start_month
			from ride
		) a, (
			select user_id
			from user
		) b
	) a
	group by 1, 2
)
select a.*, if(b.user_id is not null, 1, 0) as active_tag
from user a
left join (
	select distinct user_id
	from (
		select start_month
			 , user_id
			 , active_ride_num
			 , lag(active_ride_num, 1, null) over (partition by user_id order by start_month) lag1_active_ride_num
			 , lag(active_ride_num, 2, null) over (partition by user_id order by start_month) lag2_active_ride_num
		from active a
	) a
	where active_ride_num >= 5
	and lag1_active_ride_num >= 5
	and lag2_active_ride_num >= 5
) b on a.user_id = b.user_id