排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-27 Halo出行-通勤活跃用户标签开发  
0表示活跃？u000911从明细数据调出来，是活跃的呀
 
1表示活跃。你再检查下，你确定u000911连续3个月都有通勤记录大于5次？？
2024-12-25 Halo出行-通勤活跃用户标签开发  
得分的计算规则是什么呢？为什么耗时短得分反而低呢？
 
排名依据order by score desc, spend_time desc。得分只看准确率，跟耗时没有关系
2024-12-24 Halo出行-通勤活跃用户标签开发  
一次骑行时间跨天了，算成一天有效数据还是两天有效数据？
 
以开始时间为准

提交记录

提交日期 题目名称 提交代码
2024-12-27 Halo出行-通勤活跃用户标签开发  
select
	user_id,
max(case when 
	diff_lag_smon = 1 and diff_lead_smon = 1 and lag_cnt >= 5 and lead_cnt >= 5 then 1 else 0 end) as active_tag
from(
	select
user_id,
s_mon - lag(s_mon,1)over(partition by user_id order by s_mon)as diff_lag_smon,
lead(s_mon,1)over(partition by user_id order by s_mon) - s_mon as diff_lead_smon,
		lag(cnt,1)over(partition by user_id order by s_mon)as lag_cnt,
lead(cnt,1)over(partition by user_id order by s_mon)as lead_cnt
from(
select 
user_id, replace(left(start_time,7),'-','') as s_mon,
count(distinct (left(if(m1.loc_type != m2.loc_type,start_time,null),10))) as cnt
from(
select 
user_id, start_time,end_time,start_loc,end_loc
from hello_bike_riding_rcd 
where left(end_time,4) >= '2020' and left(start_time,4) <= '2024' 
)as r
left join (
select 
loc_nm ,loc_type
from gd_loc_map 
where loc_type = '写字楼' or loc_type = '地铁站'
)as m1
on r.start_loc = m1.loc_nm
left join (
select 
loc_nm ,loc_type
from gd_loc_map 
where loc_type = '写字楼' or loc_type = '地铁站'
)as m2
on r.end_loc = m2.loc_nm
group by user_id,replace(left(start_time,7),'-','')
)as tmp1
)as tmp2
group by user_id
order by user_id
2024-12-27 Halo出行-通勤活跃用户标签开发  
select
	user_id,
max(case when 
	diff_lag_smon = 1 and diff_lead_smon = 1 then 1 else 0 end) as active_tag
from(
	select
user_id,
s_mon - lag(s_mon,1)over(partition by user_id order by s_mon)as diff_lag_smon,
lead(s_mon,1)over(partition by user_id order by s_mon) - s_mon as diff_lead_smon
from(
select 
user_id,replace(left(start_time,7),'-','') as s_mon,
count(distinct (left(start_time,10))) as cnt
from(
select 
user_id, start_time,end_time,start_loc,end_loc
from hello_bike_riding_rcd 
where left(end_time,4) >= '2020' and left(start_time,4) <= '2024' 
)as r
join (
select 
loc_nm ,loc_type
from gd_loc_map 
where loc_type = '写字楼' or loc_type = '地铁站'
)as m1
on r.start_loc = m1.loc_nm
join (
select 
loc_nm ,loc_type
from gd_loc_map 
where loc_type = '写字楼' or loc_type = '地铁站'
)as m2
on r.end_loc = m2.loc_nm
where m1.loc_type != m2.loc_type
group by user_id,replace(left(start_time,7),'-','')
having cnt >= 5
)as tmp1
)as tmp2
group by user_id
order by user_id
2024-12-27 Halo出行-通勤活跃用户标签开发  
select
	user_id,
min(case when 
	diff_lag_smon = 1 and diff_lead_smon = 1 then 0 else 1 end) as active_tag
from(
	select
user_id,
s_mon - lag(s_mon,1)over(partition by user_id order by s_mon)as diff_lag_smon,
lead(s_mon,1)over(partition by user_id order by s_mon) - s_mon as diff_lead_smon
from(
select 
user_id,replace(left(start_time,7),'-','') as s_mon,
count(distinct (left(start_time,10))) as cnt
from(
select 
user_id, start_time,end_time,start_loc,end_loc
from hello_bike_riding_rcd 
where left(end_time,4) >= '2020' and left(start_time,4) <= '2024' 
)as r
join (
select 
loc_nm ,loc_type
from gd_loc_map 
where loc_type = '写字楼' or loc_type = '地铁站'
)as m1
on r.start_loc = m1.loc_nm
join (
select 
loc_nm ,loc_type
from gd_loc_map 
where loc_type = '写字楼' or loc_type = '地铁站'
)as m2
on r.end_loc = m2.loc_nm
where m1.loc_type != m2.loc_type
group by user_id,replace(left(start_time,7),'-','')
having cnt >= 5
)as tmp1
)as tmp2
group by user_id
order by user_id