排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-26 Halo出行-通勤活跃用户标签开发  
with 
loc as 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
)
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join loct2 
on t1.start_loc = t2.loc_nm 
left join loct3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
distinct user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
t0.user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id
from 
(
select
user_id
,substr(start_time,1,7) start_month
from hello_bike_riding_rcd
where year(start_time) between 2020 and 2024
group by 1,2 
) t0 
group by 1 
having count(1) >= 3 
) t0 
left join 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
on t0.user_id = t1.user_id 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,diff 
from 
(
select
user_id
,start_month_01
,date_add(start_month_01,interval -row_number() over(partition by user_id order by start_month_01) month) diff 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
where (t2.loc_type = '地铁站' and t3.loc_type = '写字楼')
or (t2.loc_type = '写字楼' and t3.loc_type = '地铁站')
) t 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having count(1) >= 5 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
,1 is_work_route
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
where (t2.loc_type = '地铁站' and t3.loc_type = '写字楼')
or (t2.loc_type = '写字楼' and t3.loc_type = '地铁站')
) t 
group by 1,2,3 
) t 
group by 1,2 
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
,1 is_work_route
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
where is_work_route = 1 
group by 1,2,3 
) t 
group by 1,2 
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,concat(start_month,'-01')start_month_01
,start_date 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3 
) t 
group by 1,2 
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month_01
,row_number() over(partition by user_id order by start_month_01) rn 
from 
(
select
user_id 
,start_month_01 
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month_01
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,concat(substr(start_time,1,7),'-01')start_month_01
,start_time
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2
) t 
group by 1,2 
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '购物广场'
when loc_nm = '方恒购物中心' then '购物广场'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '购物广场'
when loc_nm = '方恒购物中心' then '购物广场'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '购物广场'
when loc_nm = '方恒购物中心' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '购物广场'
when loc_nm = '方恒购物中心' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '地铁站'
when loc_nm = '方恒购物中心' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '地铁站'
when loc_nm = '方恒购物中心' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '购物中心'
			when loc_nm = '中关村电子城' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '购物中心'
			when loc_nm = '中关村电子城' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '购物中心'
			when loc_nm = '中关村电子城' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '购物中心'
			when loc_nm = '中关村电子城' then '购物中心'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select
t1.user_id 
,if(t2.user_id is not null,1,0) active_tag 
from 
( 
select
user_id 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024 
group by 1 
) t1 
left join 
(
select
user_id
,count(1) cons_3months_cnt 
from 
(
select
user_id
,date_add(start_month_01,interval -rn month) diff 
,count(1) cnt 
from 
(
select
user_id
,start_month
,start_month_01
,sum(is_work_route) work_route_days 
,if(sum(is_work_route) >= 5,1,0)is_work_route_over5days 
,row_number() over(partition by user_id order by start_month) rn 
from 
(
select
user_id 
,start_month
,start_month_01
,start_date
,max(is_work_route) is_work_route
from 
(
select
user_id 
,start_month
,concat(start_month,'-01')start_month_01
,start_date 
,start_time
,end_date
,end_time 
,start_loc 
,t2.loc_type start_loc_type 
,end_loc 
,t3.loc_type end_loc_type 
,case when t2.loc_type = '地铁站' and t3.loc_type = '写字楼' then 1 
when t2.loc_type = '写字楼' and t3.loc_type = '地铁站' then 1 
else 0 
end is_work_route
from 
(
select
user_id 
,substr(start_time,1,7) start_month 
,substr(start_time,1,10)start_date 
,start_time
,substr(end_time,1,10)end_date 
,end_time 
,start_loc 
,end_loc 
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
) t1 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t2 
on t1.start_loc = t2.loc_nm 
left join 
(
select
loc_nm
,case when loc_nm = '凯德广场' then '写字楼'
when loc_nm = '北京机床研究所' then '写字楼'
when loc_nm = '将台西' then '地铁站'
when loc_nm = '新世界百货' then '写字楼'
when loc_nm = '方恒购物中心' then '地铁站'
else loc_type
end loc_type
from gd_loc_map
) t3 
on t1.end_loc = t3.loc_nm 
) t 
group by 1,2,3,4 
) t 
group by 1,2,3
having if(sum(is_work_route) >= 5,1,0) = 1 
) t 
group by 1,2
having count(1) >= 3 
) t 
group by 1 
) t2 
on t1.user_id = t2.user_id