排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.start_loc
,case when loc_type ='将台西' then '地铁站' else loc_typeend as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distincta.end_loc
,case when loc_type ='将台西' then '地铁站' else loc_typeend as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from hello_bike_riding_rcd a
join start_loc_type b
on a.start_loc=b.start_loc
join end_loc_type c
on a.end_loc=c.end_loc
where substr(a.start_time,1,4) between '2020' and '2024'
and s_loc_type in ('写字楼','地铁站')
and e_loc_type in ('地铁站','写字楼')
)a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.start_loc
,case when loc_type ='将台西' then '地铁站' else loc_typeend as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distincta.end_loc
,case when loc_type ='将台西' then '地铁站' else loc_typeend as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from hello_bike_riding_rcd a
join start_loc_type b
on a.start_loc=b.start_loc
join end_loc_type c
on a.end_loc=c.end_loc
where substr(a.start_time,1,4) between '2020' and '2024'
and (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.start_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distincta.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from hello_bike_riding_rcd a
join start_loc_type b
on a.start_loc=b.start_loc
join end_loc_type c
on a.end_loc=c.end_loc
where substr(a.start_time,1,4) between '2020' and '2024'
and (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where b.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd
where substr(start_time,1,4) between '2020' and '2024') a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where b.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then '1' else '0' end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,substr(day,1,7)ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id,substr(day,1,7) )
,active_uid as
(select distinct a.user_id
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>='5')a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,substr(day,1,7)ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)
select distinct a.user_id
,case when b.user_id is not null then '1' else '0' end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id
,concat(substr(day,1,4),substr(day,6,2)) )
,active_uid as
(select distinct user_id
from
(select distinct a.user_id,a.ym,a.l1,a.l2
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
join 
(select user_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,concat(substr(day,1,4),substr(day,6,2))ym
from
(select distinct '整体'user_id,substr(start_time,1,10)day
from hello_bike_riding_rcd) a
)a
group by user_id,ym) b
on a.ym=b.ym 
and a.l1=b.l1
and a.l2=b.l2
where a.l2 is not null 
)a
)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id
,concat(substr(day,1,4),substr(day,6,2)) )
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
 )a
where gap>=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(select user_id
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from
(select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼'))a
group by user_id
,concat(substr(day,1,4),substr(day,6,2)) )
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select distinct user_id,ym
from user_metro_office_bymonth
where times>=5)a
group by user_id,ym)a
 )a
where gap=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
where substr(a.start_time,1,4) between '2020' and '2024'
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select user_id
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap>=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select user_id
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap>=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select user_id
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap>=3)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
from
(select user_id
,substr(day,1,7)t
,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,substr(day,1,7),concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or (s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,r,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
,row_number()over(partition by user_id order by ym asc)r
from
(select user_id
 ,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
and (s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,r,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
,row_number()over(partition by user_id order by ym asc)r
from
(select user_id
 ,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
or(s_loc_type='地铁站' and e_loc_type='写字楼')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,r,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
,row_number()over(partition by user_id order by ym asc)r
from
(select user_id
 ,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with start_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as s_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.start_loc=b.loc_nm
)
,end_loc_type as 
(select distinct a.user_id,a.start_time,a.end_time,a.start_loc,a.end_loc,b.loc_type as e_loc_type
from hello_bike_riding_rcd a
left join gd_loc_map b
on a.end_loc=b.loc_nm
)
,user_metro_office_bymonth as 
(
select distinct a.user_id
,substr(a.start_time,1,10)day
from start_loc_type a
join end_loc_type b
on a.user_id=b.user_id 
and a.start_time=b.start_time
and a.end_time=b.end_time
and a.start_loc=b.start_loc
and a.end_loc=b.end_loc
where (s_loc_type='写字楼' and e_loc_type='地铁站')
)
,active_uid as
(select distinct user_id
from
(select distinct user_id,ym,l1,l2,r,ym-l2 as gap
from
(selectuser_id,ym
,lag(ym,1)over(partition by user_id order by ym asc)l1
,lag(ym,2)over(partition by user_id order by ym asc)l2
,row_number()over(partition by user_id order by ym asc)r
from
(select user_id
 ,concat(substr(day,1,4),substr(day,6,2))ym
,count(distinct day)times
from user_metro_office_bymonth
group by user_id,concat(substr(day,1,4),substr(day,6,2)))a
where times>=5
group by user_id,ym)a
 )a
where gap=2)
select distinct a.user_id
,case when b.user_id is not null then 1 else 0 end as active_tag
from
(select distinct user_id
from hello_bike_riding_rcd ) a
left join active_uid b
on a.user_id=b.user_id