排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-01-10 小宇宙电台的同期群分析  
我也求一个答案
 
啥也没说
2025-01-08 Halo出行-通勤活跃用户标签开发  
不耗了，等着听思路检查思路，被“已有业务同事反映部分地点分类不准的情况”闹麻了，维度表如果是数仓工程师维护的话他该滚蛋了，要是数分维护的话我滚蛋hhh
 
啥也没说
2025-01-08 Halo出行-通勤活跃用户标签开发  
我感觉自己思路没问题啊，但是就是不能满分
 
提示：有两个分错了。可以打开北京地图 望京片区看看
2025-01-08 Halo出行-通勤活跃用户标签开发  
难绷
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-01-10 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
and start_loc not in ('恒通国际商务园')
and end_loc not in ('恒通国际商务园')
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-10 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
and start_loc not in ('中关村电子城')
and end_loc not in ('中关村电子城')
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-10 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
and start_loc not in ('北京机床研究所')
and end_loc not in ('北京机床研究所')
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2=1 and bq1=1,1,0) active_tag
from
(
select
user_id,
bq1,
dt_month_minus,
if(count(1)>=3,1,0) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2=1 and bq1=1,1,0) active_tag
from
(
select
user_id,
bq1,
dt_month_minus,
if(count(1)>=3,1,0) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
								 when loc_nm = '中关村电子城' then '购物广场'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2=1 and bq1=1,1,0) active_tag
from
(
select
user_id,
bq1,
dt_month_minus,
if(count(1)>=3,1,0) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '写字楼'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '恒通国际商务园' then '写字楼'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '恒通国际商务园' then '购物广场'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '望京' then '购物广场'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '望京' then '写字楼'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '凯德广场' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '凯德广场' then '购物广场'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '凯德广场' then '写字楼'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '中关村电子城' then '写字楼'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '中关村电子城' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '中关村电子城' then '购物广场'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 when loc_nm = '恒通国际商务园' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '购物广场'
 when loc_nm = '将台西' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '地铁站'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1
2025-01-08 Halo出行-通勤活跃用户标签开发  
select
user_id,
max(active_tag) active_tag
from
(
select
user_id,
if(bq2>=3 and bq1=1,1,0) active_tag
from
(
select
user_id,
dt_month_minus,
bq1,
sum(bq1) bq2
from
(
select
user_id,
dt_month,
rk,
date_format(date_sub(str_to_date(concat(dt_month, '-01'),'%Y-%m-%d'), interval rk month), '%Y-%m') dt_month_minus,
bq1
from
(
select
user_id,
dt_month,
rank() over(partition by user_id order by str_to_date(concat(dt_month, '-01'),'%Y-%m-%d')) rk,
if(sum(bq)>=5,1,0) bq1
from
(
select
user_id,
date_format(dt, '%Y-%m') dt_month,
dt,
max(case when t1.start_type!= t1.end_type and t1.start_type in ('写字楼','地铁站') and t1.end_type in ('写字楼','地铁站')
 then 1 else 0 end) bq
from
(
with glm as
(
select
loc_nm,
case when loc_nm = '北京机床研究所' then '写字楼'
 when loc_nm = '将台西' then '写字楼'
 else loc_type
end loc_type
from gd_loc_map
)
select
hbrr.user_id user_id,
date(hbrr.start_time) dt,
glm1.loc_type start_type,
glm2.loc_type end_type
from hello_bike_riding_rcd hbrr
left join glm glm1
on hbrr.start_loc = glm1.loc_nm
left join glm glm2
on hbrr.end_loc = glm2.loc_nm
where year(hbrr.start_time) between 2020 and 2024
and year(hbrr.end_time) between 2020 and 2024
) t1
group by 1,2,3
) t2
group by 1,2
) t3
) t4
group by 1,2,3
) t5
) t6
group by 1