排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-26 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t3.user_id is not null then 1 else 0 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd)t
left join 
(
select user_id
from
(selectuser_id,
months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(select user_id,months, count(distinct day(start_time)) days_cnt
 from (SELECT a.id,a.user_id,
	(year(a.start_time)-2019)*12+month(a.start_time) as months,a.start_time
from hello_bike_riding_rcd a 
left join (
select loc_nm,
case when loc_nm ='将台西' then '地铁站'
	when loc_nm='凯德广场' then '购物广场' else loc_type end as loc_type 
from gd_loc_map
) b on a.start_loc=b.loc_nm
left join (
select loc_nm,
case when loc_nm ='将台西' then '地铁站'
	when loc_nm='凯德广场' then '购物广场' else loc_type end as loc_type 
from gd_loc_map
) c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站')
)tt
group by user_id, months)t1
where days_cnt>=5
)t2
where months_sub=0 and month_add=-1
)t3 
on t.user_id=t3.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t3.user_id is not null then 1 else 0 end as active_tag
from 
 (select distinct user_id from hello_bike_riding_rcd)t
 left join
(select user_id
from 
(select user_id, months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
 (select user_id,months,count(distinct days)as day_cnt
from 
(SELECT a.user_id,
	(year(a.start_time)-2020)*12+month(a.start_time) as months,day(a.start_time) as days
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站'))t1
group by user_id,months)t2 
where day_cnt>4)t4
where months_sub=0 and month_add=-1)t3
on t.user_id=t3.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t3.user_id is not null then 1 else 0 end as active_tag
from 
 (select distinct user_id from hello_bike_riding_rcd)t
 left join
(select user_id
from 
(select user_id,months,count(distinct days)as day_cnt,months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(SELECT a.user_id,
	(year(a.start_time)-2020)*12+month(a.start_time) as months,day(a.start_time) as days
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站'))t1
group by user_id,months)t2 
where day_cnt>4 and months_sub=0 and month_add=-1)t3
on t.user_id=t3.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t3.user_id is not null then 1 else 0 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd)t
left join 
(
select user_id
from
(selectuser_id,
months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(select user_id,months, count(distinct day(start_time)) days_cnt
 from (SELECT a.id,a.user_id,
	(year(a.start_time)-2019)*12+month(a.start_time) as months,a.start_time
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站'))tt
group by user_id, months)t1
where days_cnt>=5
)t2
where months_sub=0 and month_add=-1
)t3 
on t.user_id=t3.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t2.user_id is not null then 1 else 0 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd)t
left join 
(
select user_id
from (selectuser_id,
months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(select user_id,months
 from (SELECT a.id,a.user_id,
	(year(a.start_time)-2019)*12+month(a.start_time) as months
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站'))tt
group by user_id, months
having count(id)>=5 )t1)t3
where months_sub=0 and month_add=-1
)t2 
on t.user_id=t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t2.months_sub=0 and t2.month_add=-1 then 1 else 0 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd)t
left join 
(selectuser_id,
months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(select user_id,months
 from (SELECT a.id,a.user_id,
	(year(a.start_time)-2019)*12+month(a.start_time) as months
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站'))tt
group by user_id, months
having count(id)>=5 )t1)t2 
on t.user_id=t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t2.months_sub=0 and t2.month_add=-1 then 1 else 0 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd)t
left join 
(selectuser_id,
months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(select user_id,months
 from (SELECT a.id,a.user_id,
	(year(a.start_time)-2019)*month(a.start_time) as months
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站'))tt
group by user_id, months
having count(id)>=5 )t1)t2 
on t.user_id=t2.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
select t.user_id,
case when t2.months_sub=0 and t2.month_add=-1 then 1 else 0 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd)t
left join 
(selectuser_id,
months-lag(months)over(partition by user_id order by months)+months-lead(months)over(partition by user_id order by months) months_sub,
months-lead(months)over(partition by user_id order by months)as month_add
from 
(SELECT user_id,
	(year(start_time)-2020)*month(start_time) as months
from hello_bike_riding_rcd a 
left join gd_loc_map b on a.start_loc=b.loc_nm
left join gd_loc_map c on a.end_loc=c.loc_nm 
where (b.loc_type='写字楼' and c.loc_type='地铁站') 
	or (c.loc_type='写字楼' and b.loc_type='地铁站')
group by user_id,(year(start_time)-2020)*month(start_time)
having count(id)>=5 )t1)t2 
on t.user_id=t2.user_id