排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-26 小宇宙电台的同期群分析  已解决
2024-12-25 Halo出行-通勤活跃用户标签开发  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-26 小宇宙电台的同期群分析  
为啥我的结果的日期和示例总是岔一天
 
日期在动，没关系的

提交记录

提交日期 题目名称 提交代码
2025-01-13 小宇宙电台的同期群分析  
with t1 as
(
select
usr_id
,substr(login_time,1,10) dt
from user_login_log
group by 1,2
	)
,t2 as
	(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from t1
)
,t3 as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from t2
)
select
	dt login_date
,concat_ws(', ',round(sum(case when tag = 1 then 1 end)/count(1) * 100,2),round(count(case when tag = 2 then 1 end)/count(1) * 100,2),round(count(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from t3
where dt >= '2024-01-01'
group by 1
order by 1
2025-01-13 小宇宙电台的同期群分析  
with t1 as
(
select
usr_id
,substr(login_time,1,10) dt
from user_login_log
group by 1,2
	)
,t2 as
	(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from t1
)
,t3 as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from t2
)
select
	dt login_date
,concat_ws(', ',round(count(case when tag = 1 then 1 end)/count(1) * 100,2),round(count(case when tag = 2 then 1 end)/count(1) * 100,2),round(count(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from t3
where dt >= '2024-01-01'
group by 1
order by 1
2025-01-13 小宇宙电台的同期群分析  
with tag as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from 
(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from
(
select
usr_id
,date_format(login_time,'%Y-%m-%d') dt
from user_login_log
group by 1,2
) t1
) tt1
)
select
	dt login_date
,concat_ws(', ',round(count(case when tag = 1 then 1 end)/count(1) * 100,2),round(count(case when tag = 2 then 1 end)/count(1) * 100,2),round(count(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from tag
where dt >= '2024-01-01'
group by 1
order by 1
2025-01-13 小宇宙电台的同期群分析  
with tag as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from 
(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from
(
select
usr_id
,date_format(login_time,'%Y-%m-%d') dt
from user_login_log
group by 1,2
order by 1,2
) t1
) tt1
)
select
	dt login_date
,concat_ws(', ',round(count(case when tag = 1 then 1 end)/count(1) * 100,2),round(count(case when tag = 2 then 1 end)/count(1) * 100,2),round(count(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from tag
where dt >= '2024-01-01'
group by 1
order by 1
2025-01-13 小宇宙电台的同期群分析  
with t1 as
(
select
usr_id
,substr(login_time,1,10) dt
from user_login_log
group by 1,2
	)
,t2 as
	(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from t1
)
,t3 as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from t2
)
select
	dt login_date
,concat_ws(', ',round(sum(case when tag = 1 then 1 end)/count(1) * 100,2),round(sum(case when tag = 2 then 1 end)/count(1) * 100,2),round(sum(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from t3
where dt >= '2024-01-01'
group by 1
2024-12-30 小宇宙电台的同期群分析  
with tag as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from 
(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from
(
select
usr_id
,date(login_time) dt
from user_login_log
group by 1,2
order by 1,2
) t1
) tt1
)
select
	dt login_date
,concat_ws(', ',round(count(case when tag = 1 then 1 end)/count(1) * 100,2),round(count(case when tag = 2 then 1 end)/count(1) * 100,2),round(count(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from tag
where dt >= '2024-01-01'
group by 1
order by 1
2024-12-30 小宇宙电台的同期群分析  
with tag as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from 
(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from
(
select
usr_id
,date_format(login_time,'%Y-%m-%d') dt
from user_login_log
group by 1,2
order by 1,2
) t1
) tt1
)
select
	dt login_date
,concat_ws(', ',round(sum(case when tag = 1 then 1 end)/count(1) * 100,2),round(sum(case when tag = 2 then 1 end)/count(1) * 100,2),round(sum(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from tag
where dt >= '2024-01-01'
group by 1
order by 1
2024-12-30 小宇宙电台的同期群分析  
with tag as
	(
select
usr_id
,dt
,case when rk = 1 then 1
when datediff(dt,dt_1) <= 3 then 2
when datediff(dt,dt_1) > 3 then 3
else 0 end tag
from 
(
select
usr_id
,dt
,lag(dt,1)over(partition by usr_id order by dt) dt_1
,row_number()over(partition by usr_id order by dt) rk
from
(
select
usr_id
,date(login_time) dt
from user_login_log
group by 1,2
) t1
) tt1
)
select
	dt login_date
,concat_ws(', ',round(count(case when tag = 1 then 1 end)/count(1) * 100,2),round(count(case when tag = 2 then 1 end)/count(1) * 100,2),round(count(case when tag = 3 then 1 end)/count(1) * 100,2)) pct
from tag
where dt >= '2024-01-01'
group by 1
order by 1
2024-12-30 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
(
select
user_id
,date_format(start_time,'%Y-%m-01') mon
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_num + t3.loc_num = 0
group by 1,2
having count(distinct date(start_time))>=5
)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-30 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
(
select
user_id
,date_format(start_time,'%Y-%m-01') mon
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_num + t3.loc_num = 0
group by 1,2
having count(date(start_time))>=5
)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-30 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,date_format(start_day,'%Y-%m-01') mon
from
		(
select
user_id
,date_format(start_time,'%Y-%m-%d') start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-30 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,month(start_day) mon
from
		(
select
user_id
,date(start_time) start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-30 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,month(start_day) mon
from
		(
select
user_id
,date(start_time) start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) = 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,concat(substr(start_day,1,7),'-01') mon
from
		(
select
user_id
,substr(start_time,1,10) start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where substr(start_time,1,4) = 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,concat(substr(start_day,1,7),'-01') mon
from
		(
select
user_id
,substr(start_time,1,10) start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where substr(start_time,1,4) = 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,concat(substr(start_day,1,7),'-01') mon
from
		(
select
user_id
,substr(start_time,1,10) start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where substr(start_time,1,4) = 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
where exists (select user_id from tag_mon where user_id = tag_mon.user_id group by 1 having count(1)>=3)
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,concat(substr(start_day,1,7),'-01') mon
from
		(
select
user_id
,substr(start_time,1,10) start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
where exists (select user_id from tag_mon where user_id = tag_mon.user_id group by 1 having count(1)>=3)
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(
select
user_id
,date_format(start_day,'%Y-%m-01') mon
from
(
select
t4.user_id
,date_format(start_time,'%Y-%m-%d') start_day
from 
(
select
user_id
from 
(
select
user_id
,date_format(start_time,'%Y-%m-%d') start_day
,count(1)
from hello_bike_riding_rcd
group by 1,2
) t1
group by 1
having count(1) >= 15
) t4
left join hello_bike_riding_rcd t1
on t4.user_id = t1.user_id
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_num + t3.loc_num = 0
group by 1,2
)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
where exists (select user_id from tag_mon where user_id = tag_mon.user_id group by 1 having count(1)>=3)
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_mon as
	(	
	select
user_id
,date_format(start_day,'%Y-%m-01') mon
from
		(
select
user_id
,date_format(start_time,'%Y-%m-%d') start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
	and t2.loc_num + t3.loc_num = 0
group by 1,2
		)t1
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
where exists (select user_id from tag_mon where user_id = tag_mon.user_id group by 1 having count(1)>=3)
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1
2024-12-28 Halo出行-通勤活跃用户标签开发  
with gd_loc_map_corrected as
	(
select 
 loc_nm 
 ,case when loc_nm like '将%' then -1
 	 when loc_nm like '北%' or loc_type like '写%' then 1
 when loc_type like '地%' then -1 
 end loc_num
from gd_loc_map
)
,tag_day as
( 
select
user_id
,date_format(start_time,'%Y-%m-%d') start_day
from hello_bike_riding_rcd t1
left join gd_loc_map_corrected t2
on t1.start_loc = t2.loc_nm
left join gd_loc_map_corrected t3
on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_num + t3.loc_num = 0
group by 1,2
order by 1,2
)
,tag_mon as
	(	
	select
user_id
,date_format(start_day,'%Y-%m-01') mon
from tag_day
where user_id in (select user_id from tag_day group by 1 having count(1) >= 15)
group by 1,2
having count(2) >= 5
	)
,tag_part as
 	(
select
user_id
,1 as active_tag
from
(
select
user_id
,mon
,row_number()over(partition by user_id order by mon) rk
from tag_mon
where user_id in (select user_id from tag_mon group by 1 having count(1)>=3)
)t1
group by user_id,date_sub(mon,interval rk month)
having count(1)>=3
 	)
select
	g1.user_id
,coalesce(active_tag,0) active_tag
from 
	(
select
distinct user_id
from hello_bike_riding_rcd
) g1
left join tag_part g2
on g1.user_id = g2.user_id
order by 1