with t as (
select
		user_id,
date_format(start_time,'%Y-%m') `month`,
count(distinct date(start_time)) qx_dt
from hello_bike_riding_rcd
where year(start_time) between '2020'and '2024'
and(start_locin ('望京南','将台西','望京','阜通') and end_loc in ('瀚海国际大厦','北京机床研究所','天启大厦','中关村电子城','恒通国际商务园','西门子大厦')
or end_locin ('望京南','将台西','望京','阜通') and start_loc in ('瀚海国际大厦','北京机床研究所','天启大厦','中关村电子城','恒通国际商务园','西门子大厦'))
group by user_id,date_format(start_time,'%Y-%m')
having qx_dt >= 5),
t1 as (
select 
		user_id,
t.`month`,
lag(t.`month`,2) over(partition by user_id
 order by t.`month`) lx
from t),
t2 as (select user_id `ac_user`
from t1
where lx-`month` = 0 )
select 
		user_id,
case when ac_user is not null then 1
else 0
end `active_tag`
from t2
right join (select user_id from hello_bike_riding_rcd group by user_id) u
on t2.ac_user = u.user_id

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 (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;

with t as (select 
		user_id,date_format(start_time,'%Y-%m') qx_dt,count(distinct date(start_time)) qx_num
from 
		hello_bike_riding_rcd
group by
		user_id,qx_dt),
t1 as (select 
		user_id,
qx_dt,
lag(qx_dt,2) over(partition by user_id
 order by qx_dt) lx_start_month
from t 
where qx_num >= 5),
t2 as (select 
		user_id,
case when date_format(
date_sub(
str_to_date(concat(qx_dt, '-01'), '%Y-%m-%d'), 
interval 2 month
),
'%Y-%m'
) = lx_start_month then 1
else 0
end active_tag
from t1),
t3 as (select 
		user_id,active_tag
from t2
group by user_id,active_tag)
select user_id,active_tag
from t3 ;

SELECT 
	*
FROM 
	cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
	and date(trx_time) between '2024-09-01' and '2024-09-30'
order by 
    trx_time;

select *
 from cmb_usr_trx_rcd
 where usr_id = '5201314520'
 and trx_time >= '2024-09-01' 
 and trx_time < '2024-10-01'
order by trx_time ;