排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-31 Halo出行-通勤活跃用户标签开发  
with t_loc as 
(
select '将台西中关村电子城' loc
union all 
select '将台西凯德广场'
union all
select '将西台北京机床研究所'
union all 
select '将西台天启大厦'
union all 
select '中关村电子城将台西'
union all 
select '凯德广场将台西'
union all
select '北京机床研究所将西台'
union all 
select '天启大厦将西台'
)
,
t_main as 
(
select user_id,start_time
from hello_bike_riding_rcd
where concat(start_loc,end_loc) in 
(select loc from t_loc)
),
t_user as 
(
select user_id,mon
from (
select user_id,substr(start_time,1,7) mon,count(distinct substr(start_time,1,10)) cnt
from t_main 
group by user_id,substr(start_time,1,7)
)t1
where cnt >=5
),
t_row as 
(	
select user_id
	from 
	(
select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7) mon ,count(*) cnt
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
group by user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7)
) t2
 where cnt >=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-30 Halo出行-通勤活跃用户标签开发  
with t_loc as 
(
select loc_nm,
case when loc_nm = '将台西' then '地铁站'
 when loc_nm = '北京机床研究所' then '写字楼'
else loc_type end loc_type 
from gd_loc_map
)
,
t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join t_loc t2 on t1.start_loc = t2.loc_nm
left join t_loc t3 on t1.end_loc = t3.loc_nm
where concat(t2.loc_type ,'-',t3.loc_type) in ('写字楼-地铁站','地铁站-写字楼')
),
t_user as 
(
select user_id,mon
from (
select user_id,substr(start_time,1,7) mon,count(distinct substr(start_time,1,10)) cnt
from t_main 
group by user_id,substr(start_time,1,7)
)t1
where cnt >=5
),
t_row as 
(	
select user_id
	from 
	(
select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7) mon ,count(*) cnt
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
group by user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7)
) t2
 where cnt >=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-30 Halo出行-通勤活跃用户标签开发  
with t_loc as 
(
select loc_nm,
case when loc_nm = '将台西' then '地铁站'
 when loc_nm = '北京机床研究所' then '写字楼'
else loc_type end loc_type 
from gd_loc_map
)
,
t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join t_loc t2 on t1.start_loc = t2.loc_nm
left join t_loc t3 on t1.end_loc = t3.loc_nm
),
t_user as 
(
select user_id,substr(start_time,1,7) mon
from t_main 
where concat(start_type,'-',end_type) in ('写字楼-地铁站','地铁站-写字楼')
group by user_id,substr(start_time,1,7)
having count(distinct substr(start_time,1,10))>=5
),
t_row as 
(
select user_id,mon
from 
(select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7) mon 
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
) t2 
group by user_id,mon
having count(*)>=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-30 Halo出行-通勤活跃用户标签开发  
with t_loc as 
(
select loc_nm,if(loc_nm = '将台西','地铁站',loc_type) loc_type from gd_loc_map
)
,
t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join t_loc t2 on t1.start_loc = t2.loc_nm
left join t_loc t3 on t1.end_loc = t3.loc_nm
),
t_user as 
(
select user_id,substr(start_time,1,7) mon
from t_main 
where concat(start_type,'-',end_type) in ('写字楼-地铁站','地铁站-写字楼')
group by user_id,substr(start_time,1,7)
having count(distinct substr(start_time,1,10))>=5
),
t_row as 
(
select user_id,mon
from 
(select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7) mon 
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
) t2 
group by user_id,mon
having count(*)>=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-27 Halo出行-通勤活跃用户标签开发  
with t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join gd_loc_map t2 on t1.start_loc = t2.loc_nm
left join gd_loc_map t3 on t1.end_loc = t3.loc_nm
),
t_user as 
(
select user_id,substr(start_time,1,7) mon
from t_main 
where concat(start_type,'-',end_type) in ('写字楼-地铁站','地铁站-写字楼')
group by user_id,substr(start_time,1,7)
having count(distinct substr(start_time,1,10))>=5
),
t_row as 
(
select user_id,mon
from 
(select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7) mon 
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
) t2 
group by user_id,mon
having count(*)=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-27 Halo出行-通勤活跃用户标签开发  
with t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join gd_loc_map t2 on t1.start_loc = t2.loc_nm
left join gd_loc_map t3 on t1.end_loc = t3.loc_nm
),
t_user as 
(
select user_id,substr(start_time,1,7) mon
from t_main 
where concat(start_type,'-',end_type) in ('写字楼-地铁站','地铁站-写字楼')
group by user_id,substr(start_time,1,7)
having count(distinct substr(start_time,1,10))>=5
),
t_row as 
(
select user_id,mon
from 
(select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL rk MONTH),1,7) mon 
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
) t2 
group by user_id,mon
having count(*)>=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-27 Halo出行-通勤活跃用户标签开发  
with t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join gd_loc_map t2 on t1.start_loc = t2.loc_nm
left join gd_loc_map t3 on t1.end_loc = t3.loc_nm
),
t_user as 
(
select user_id,substr(start_time,1,7) mon
from t_main 
where concat(start_type,'-',end_type) in ('写字楼-地铁站','地铁站-写字楼')
group by user_id,substr(start_time,1,7)
having count(distinct substr(start_time,1,10))>=5
),
t_row as 
(
select user_id,mon
from 
(select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH),1,7) mon 
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
) t2 
group by user_id,mon
having count(*)>=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)
2024-12-27 Halo出行-通勤活跃用户标签开发  
with t_main as 
(
select t1.*,t2.loc_type start_type ,t3.loc_type end_type
from hello_bike_riding_rcd t1 
left join gd_loc_map t2 on t1.start_loc = t2.loc_nm
left join gd_loc_map t3 on t1.start_loc = t3.loc_nm
),
t_user as 
(
select user_id,substr(start_time,1,7) mon
from t_main 
where concat(start_type,'-',end_type) in ('写字楼-地铁站','地铁站-写字楼')
group by user_id,substr(start_time,1,7)
having count(distinct substr(start_time,1,10))>=5
),
t_row as 
(
select user_id,mon
from 
(select user_id,substr(DATE_SUB(STR_TO_DATE(CONCAT(mon, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH),1,7) mon 
from 
(select user_id,mon,row_number()over(partition by user_id order by mon) rk 
from t_user 
) t1
) t2 
group by user_id,mon
having count(*)>=3
)
select t1.user_id
,if(t2.user_id is null ,0,1) active_tag 
from hello_bike_riding_rcd t1 left join t_row t2 on t1.user_id = t2.user_id
group by t1.user_id,if(t2.user_id is null ,0,1)