select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join
(
select user_idfrom
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id, count( distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( distinct substr(start_time,1,10)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join
(
select user_idfrom
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id, count( distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站'when loc_nm ='凯德广场'then '购物广场'when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站'when loc_nm ='凯德广场'then '购物广场'when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( distinct substr(start_time,1,10)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join
(
select user_idfrom
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id, count( distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站'when loc_nm ='凯德广场'then ' 购物广场'when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站'when loc_nm ='凯德广场'then ' 购物广场'when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( distinct substr(start_time,1,10)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join
(
select user_idfrom
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count( *) from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( *) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd where substr(start_time,1,4) between '2020' and '2024' group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,7))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,7)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,7))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,7)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
( start_time-lead(start_time,1,0)over(partition by user_id order by start_time) ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,7))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,7)) >=5
) a1) a2
where minusc =0
group by user_idhaving count(minusc) >= 3) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
( start_time-lead(start_time,1,0)over(partition by user_id order by start_time) ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm = '将台西' then '地铁站' when loc_nm ='北京机床研究所'
then '写字楼'
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,10)) >=5
) a1) a2
where minusc =0
group by user_idhaving count(minusc) >= 3) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
( start_time-lead(start_time,1,0)over(partition by user_id order by start_time) ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm = '将台西' then '地铁站' else loc_typeend loc_type from gd_loc_map ) b
on a.start_loc = b.loc_nm
left join( select loc_nm,case when loc_nm = '将台西' then '地铁站' else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,10)) >=5
) a1) a2
where minusc =0
group by user_idhaving count(minusc) >= 3) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
( start_time-lead(start_time,1,0)over(partition by user_id order by start_time) ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join gd_loc_map b
on a.start_loc = b.loc_nm
left joingd_loc_map c
on a.end_loc = c.loc_nm
where
substr(start_time,1,4) between '2020' and '2024'
and concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,10)) >=5
) a1) a2
where minusc =0
group by user_idhaving count(minusc) >= 3) a2
on a3.user_id = a2.user_id
;
select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join
(
select user_idfrom
(
select user_id,
( start_time-lead(start_time,1,0)over(partition by user_id order by start_time) ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join gd_loc_map b
on a.start_loc = b.loc_nm
left joingd_loc_map c
on a.end_loc = c.loc_nm
whereconcat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
and substr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,10)) >=5
) a1) a2
where minusc =0
group by user_idhaving count(minusc) >= 3) a2
on a3.user_id = a2.user_id
;