WITH commute_records AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
FROM hello_bike_riding_rcd r
WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM commute_records m1
JOIN commute_records m2 
JOIN commute_records m3 
WHERE m2.user_id = m1.user_id 
AND m3.user_id = m1.user_id
AND m2.commute_month = m1.commute_month + 1
AND m3.commute_month = m2.commute_month + 1
)
SELECT 
u.user_id,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM 
hello_bike_riding_rcd h join(
SELECT DISTINCT user_id 
FROM commute_records
) u
on h.user_id = u.user_id
LEFT JOIN active_users a ON u.user_id = a.user_id
;