with ks_live_t1_t2 as (
select
ks_live_t1.usr_id
,ks_live_t1.live_id
,ks_live_t1.enter_time
,ks_live_t1.leave_time
,ks_live_t2.live_nm
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
)
select
live_id
,live_nm
,count(distinct usr_id) as online_users
from ks_live_t1_t2
where enter_time <= '2021-9-12 23:48:38'
and leave_time >= '2021-9-12 23:48:38'
group by 
live_id
,live_nm
order by 
online_users desc

with ks_live_t1_t2 as (
select
ks_live_t1.usr_id
,ks_live_t1.live_id
,ks_live_t1.enter_time
,ks_live_t1.leave_time
,ks_live_t2.live_nm
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
)
select
live_id
,live_nm
,count(*) as online_users
from ks_live_t1_t2
where enter_time <= '2021-9-12 23:48:38'
and leave_time >= '2021-9-12 23:48:38'
group by 
live_id
,live_nm
order by 
online_users desc

select 
 date(u1.login_time) as login_date
 ,concat(round(count(distinct u2.usr_id)/count(distinct u1.usr_id)*100, 2), '%') as T1_retention_rate
from user_login_log as u1
left join user_login_log as u2
on u1.usr_id = u2.usr_id and date_add(date(u1.login_time), interval 1 day) = date(u2.login_time)
where datediff(curdate(), u1.login_time) <= 30
group by date(u1.login_time)
order by login_date

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date_format(start_time,'%Y%m') as start_time_month
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
riding_times_count as (
select
user_id
,start_time_month
,count(*) as riding_times_count
from start_end_loc_map
group by
user_id
,start_time_month
having count(*) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date_format(start_time,'%Y%m') as start_time_month
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
riding_times_count as (
select
user_id
,start_time_month
,count(*) as riding_times_count
from start_end_loc_map
group by
user_id
,start_time_month
having count(*) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, previous_two_record) = 2
)
select
r.user_id
,case when active_customer.user_id is not null then 1
else 0
 end as active_tag
from 
(select distinct user_id from hello_bike_riding_rcd) r
left join active_customer
on r.user_id = active_customer.user_id
order by r.user_id

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(*) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(*) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, previous_two_record) = 2
)
select
r.user_id
,case when active_customer.user_id is not null then 1
else 0
 end as active_tag
from 
(select distinct user_id from hello_bike_riding_rcd) r
left join active_customer
on r.user_id = active_customer.user_id
order by r.user_id

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
riding_times_count as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,count(date_format(start_time_day,'%Y%m')) as riding_times_count
from start_end_loc_map
group by
user_id
,date_format(start_time_day,'%Y%m')
having count(date_format(start_time_day,'%Y%m')) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, previous_two_record) = 2
)
select
r.user_id
,case when active_customer.user_id is not null then 1
else 0
 end as active_tag
from 
(select distinct user_id from hello_bike_riding_rcd) r
left join active_customer
on r.user_id = active_customer.user_id
order by r.user_id

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, previous_two_record) = 2
)
select
r.user_id
,case when active_customer.user_id is not null then 1
else 0
 end as active_tag
from 
(select distinct user_id from hello_bike_riding_rcd) r
left join active_customer
on r.user_id = active_customer.user_id
order by r.user_id

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, previous_two_record) = 2
)
select
r.user_id
,case when active_customer.user_id is not null then 1
else 0
 end as active_tag
from 
(select distinct user_id from hello_bike_riding_rcd) r
left join active_customer
on r.user_id = active_customer.user_id
order by r.user_id

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ( '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ('凯德广场','中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('凯德广场','中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ( '方恒购物中心', '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('方恒购物中心', '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ( '新世界百货', '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('新世界百货', '凯德广场', '北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ( '北京机床研究所', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('北京机床研究所', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ( '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ( '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
from hello_bike_riding_rcd
where (start_loc in ('北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
,start_loc
,end_loc
from hello_bike_riding_rcd
where (start_loc in ('北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('北京机床研究所', '中关村电子城', '天启大厦', '恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
,start_loc
,end_loc
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

with start_end_loc_map as (
select 
distinct
user_id
,date(start_time) as start_time_day
,date(end_time) as end_time_day
,start_loc
,end_loc
from hello_bike_riding_rcd
where (start_loc in ('北京机床研究所', '中关村电子城', '天启大厦恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('北京机床研究所', '中关村电子城', '天启大厦恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
start_end_time as (
select
user_id
,date_format(start_time_day,'%Y%m') as start_time_month
,date_format(end_time_day,'%Y%m') as end_time_month
,start_loc
,end_loc
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time_month
,count(start_time_month) as riding_times_count
from start_end_time
group by
user_id
,start_time_month
having count(start_time_month) >= 5
order by
user_id
,start_time_month
),
continous_record as (
select
user_id
,start_time_month
,lag(start_time_month,1) over (partition by user_id order by start_time_month) as previous_one_record
,lag(start_time_month,2) over (partition by user_id order by start_time_month ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct 
 user_id
 ,period_diff (start_time_month, previous_one_record) as month_difference_1
 ,period_diff (start_time_month, previous_two_record) as month_difference_2
from continous_record
where period_diff (start_time_month, previous_one_record) = 1 and period_diff (start_time_month, 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

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 ('北京机床研究所', '中关村电子城', '天启大厦恒通国际商务园', '瀚海国际大厦', '西门子大厦') and end_loc in ('望京', '望京南', '阜通', '将台西')) 
or (start_loc in ('望京', '望京南', '阜通', '将台西') and end_loc in ('北京机床研究所', '中关村电子城', '天启大厦恒通国际商务园', '瀚海国际大厦', '西门子大厦')) 
),
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