排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-11-25 Halo出行-通勤活跃用户标签开发  
with loc as (
select 
d.*,
g1.loc_type as start_type,
g2.loc_type as end_type
from hello_bike_riding_rcd as d
join gd_loc_map g1 on g1.loc_nm = d.start_loc
join gd_loc_map g2 on g2.loc_nm = d.end_loc
where d.start_time >= '2020-01-01' and d.start_time < '2025-01-01'
),
commute as (
select
user_id,
DATE(l.start_time) as ride_day,
(year(l.start_time)*12 + month(l.start_time)) as month_num
from loc l
where (start_type = '写字楼' and end_type = '地铁站')
or (start_type = '地铁站' and end_type = '写字楼')
),
commute_raw as (
select distinct
user_id,
ride_day,
month_num
from commute c
),
month_day as (
select
user_id,
month_num,
count(*) as month_day_cnt
from commute_raw cr
group by user_id, month_num
),
month_5 as (
select
user_id,
month_num,
month_day_cnt,
case when month_day_cnt >= 5 then 1 else 0 end as month_active_flag
from month_day as md
),
active_month as (
select
user_id,
month_num,
month_active_flag,
case when month_active_flag = 1 then month_num - row_number() over(partition by user_id order by month_num) end as grp
from month_5
),
streak as (
select
user_id,
max(month_cnt) as longest_month
from (
select
user_id,
grp,
	count(*) as month_cnt
from active_month
where month_active_flag = 1
group by user_id, grp
) t
group by user_id
)
select
	u.user_id,
case when coalesce(s.longest_month, 0) >= 3 then 1 else 0 end as active_tag
FROM (SELECT DISTINCT user_id FROM hello_bike_riding_rcd) u
LEFT JOIN streak s ON u.user_id = s.user_id;
2025-11-25 Halo出行-通勤活跃用户标签开发  
with loc as (
select 
d.*,
g1.loc_type as start_type,
g2.loc_type as end_type
from hello_bike_riding_rcd as d
join gd_loc_map g1 on g1.loc_nm = d.start_loc
join gd_loc_map g2 on g2.loc_nm = d.end_loc
where d.start_time >= '2020-01-01' and d.start_time < '2025-01-01'
),
commute as (
select
user_id,
DATE(l.start_time) as ride_day,
(year(l.start_time)*12 + month(l.start_time)) as month_num
from loc l
where (start_type = '写字楼' and end_type = '地铁站')
or (start_type = '地铁站' and end_type = '写字楼')
),
commute_raw as (
select distinct
user_id,
ride_day,
month_num
from commute c
),
month_day as (
select
user_id,
month_num,
count(*) as month_day_cnt
from commute_raw cr
group by user_id, month_num
),
month_5 as (
select
user_id,
month_num,
month_day_cnt,
case when month_day_cnt >= 10 then 1 else 0 end as month_active_flag
from month_day as md
),
active_month as (
select
user_id,
month_num,
month_active_flag,
case when month_active_flag = 1 then month_num - row_number() over(partition by user_id order by month_num) end as grp
from month_5
),
streak as (
select
user_id,
max(month_cnt) as longest_month
from (
select
user_id,
grp,
	count(*) as month_cnt
from active_month
where month_active_flag = 1
group by user_id, grp
) t
group by user_id
)
select
	u.user_id,
case when coalesce(s.longest_month, 0) >= 3 then 1 else 0 end as active_tag
FROM (SELECT DISTINCT user_id FROM hello_bike_riding_rcd) u
LEFT JOIN streak s ON u.user_id = s.user_id;