select
t0.user_id
,case when t1.user_id is not null then 1 else 0 end as active_tag
from
(select
user_id
from
hello_bike_riding_rcd
group by
user_id) t0
left join
(select
user_id
from
(select
user_id
,start_mth
,substr(date_sub(concat(t0.start_mth,"-01"),interval 2 month),1,7) as last2_mth
,lag(start_mth) over(partition by user_id order by start_mth asc) as last_trip_mth
,lag(start_mth,2) over(partition by user_id order by start_mth asc) as last2_trip_mth
from
(select
t0.user_id
,substr(t0.start_dt,1,7) as start_mth
,count(distinct t0.start_dt) as trip_cnt
from
(select
t0.id
,t0.user_id
,t0.start_time
,substr(t0.start_time,1,10) as start_dt
,t0.end_time
,substr(t0.end_time,1,10) as end_dt
,t0.start_loc
,t1.loc_type as start_type
,t0.end_loc
,t2.loc_type as end_type
from
hello_bike_riding_rcd t0
left join
(select
loc_nm
,case when loc_nm="北京机床研究所" then "写字楼"
when loc_nm="将台西" then "地铁站"
else loc_type
end as loc_type
from
gd_loc_map) t1 on t1.loc_nm=t0.start_loc
left join
(select
loc_nm
,case when loc_nm="北京机床研究所" then "写字楼"
when loc_nm="将台西" then "地铁站"
else loc_type
end as loc_type
from
gd_loc_map) t2 on t2.loc_nm=t0.end_loc) t0
where (t0.start_type="写字楼" and t0.end_type="地铁站") or (t0.end_type="写字楼" and t0.start_type="地铁站")
group by
t0.user_id
,substr(t0.start_dt,1,7)
) t0
where t0.trip_cnt>=5) t0
where t0.last2_mth=t0.last2_trip_mth
group by
t0.user_id
) t1 on t1.user_id=t0.user_id
select
t0.user_id
,case when t1.user_id is not null then 1 else 0 end as active_tag
from
(select
user_id
from
hello_bike_riding_rcd
group by
user_id) t0
left join
(select
user_id
from
(select
user_id
,start_mth
,substr(date_sub(concat(t0.start_mth,"-01"),interval 2 month),1,7) as last2_mth
,lag(start_mth,2) over(partition by user_id order by start_mth asc) as last2_trip_mth
from
(select
t0.user_id
,substr(t0.start_dt,1,7) as start_mth
,count(distinct t0.start_dt) as trip_cnt
from
(select
t0.id
,t0.user_id
,t0.start_time
,substr(t0.start_time,1,10) as start_dt
,t0.end_time
,substr(t0.end_time,1,10) as end_dt
,t0.start_loc
,t1.loc_type as start_type
,t0.end_loc
,t2.loc_type as end_type
from
hello_bike_riding_rcd t0
left join
(select
loc_nm
,case when loc_nm="北京机床研究所" then "写字楼"
else loc_type
end as loc_type
from
gd_loc_map) t1 on t1.loc_nm=t0.start_loc
left join
(select
loc_nm
,case when loc_nm="北京机床研究所" then "写字楼"
else loc_type
end as loc_type
from
gd_loc_map) t2 on t2.loc_nm=t0.end_loc) t0
where (t0.start_type="写字楼" and t0.end_type="地铁站") or (t0.end_type="写字楼" and t0.start_type="地铁站")
group by
t0.user_id
,substr(t0.start_dt,1,7)
) t0
where t0.trip_cnt>=5) t0
where t0.last2_mth=t0.last2_trip_mth
group by
t0.user_id
) t1 on t1.user_id=t0.user_id
select
t0.user_id
,case when t1.user_id is not null then 1 else 0 end as active_tag
from
(select
user_id
from
hello_bike_riding_rcd
group by
user_id) t0
left join
(select
user_id
from
(select
user_id
,start_mth
,substr(date_sub(concat(t0.start_mth,"-01"),interval 2 month),1,7) as last2_mth
,lag(start_mth,2) over(partition by user_id order by start_mth asc) as last2_trip_mth
from
(select
t0.user_id
,substr(t0.start_dt,1,7) as start_mth
,count(distinct t0.start_dt) as trip_cnt
from
(select
t0.id
,t0.user_id
,t0.start_time
,substr(t0.start_time,1,10) as start_dt
,t0.end_time
,substr(t0.end_time,1,10) as end_dt
,t0.start_loc
,t1.loc_type as start_type
,t0.end_loc
,t2.loc_type as end_type
from
hello_bike_riding_rcd t0
left join
gd_loc_map t1 on t1.loc_nm=t0.start_loc
left join
gd_loc_map t2 on t2.loc_nm=t0.end_loc) t0
where (t0.start_type="写字楼" and t0.end_type="地铁站") or (t0.end_type="写字楼" and t0.start_type="地铁站")
group by
t0.user_id
,substr(t0.start_dt,1,7)
) t0
where t0.trip_cnt>=5) t0
where t0.last2_mth=t0.last2_trip_mth
group by
t0.user_id
) t1 on t1.user_id=t0.user_id
select
t0.user_id
,case when t1.user_id is not null then 1 else 0 end as active_tag
from
(select
user_id
from
hello_bike_riding_rcd
group by
user_id) t0
left join
(select
user_id
from
(select
user_id
,start_mth
,substr(date_sub(concat(t0.start_mth,"-01"),interval 2 month),1,7) as last2_mth
,lag(start_mth,2) over(partition by user_id order by start_mth asc) as last2_trip_mth
from
(select
t0.user_id
,substr(t0.start_dt,1,7) as start_mth
,count(distinct t0.start_dt) as trip_cnt
from
(select
t0.id
,t0.user_id
,t0.start_time
,substr(t0.start_time,1,10) as start_dt
,t0.end_time
,substr(t0.end_time,1,10) as end_dt
,t0.start_loc
,t1.loc_type as start_type
,t0.end_loc
,t2.loc_type as end_type
from
hello_bike_riding_rcd t0
left join
(select
loc_nm
,case when loc_nm="北京机床研究所" then "写字楼"
when loc_nm="凯德广场" then "购物广场"
else loc_type
end as loc_type
from
gd_loc_map) t1 on t1.loc_nm=t0.start_loc
left join
(select
loc_nm
,case when loc_nm="北京机床研究所" then "写字楼"
when loc_nm="凯德广场" then "购物广场"
else loc_type
end as loc_type
from
gd_loc_map) t2 on t2.loc_nm=t0.end_loc) t0
where (t0.start_type="写字楼" and t0.end_type="地铁站") or (t0.end_type="写字楼" and t0.start_type="地铁站")
group by
t0.user_id
,substr(t0.start_dt,1,7)
) t0
where t0.trip_cnt>=5) t0
where t0.last2_mth=t0.last2_trip_mth
group by
t0.user_id
) t1 on t1.user_id=t0.user_id