排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-25 小宇宙电台的同期群分析  
select t2.login_time as login_date,count(dn) as pct
from(
with t as (
select usr_id,login_time,
DATEDIFF(login_time,
lag(login_time) over (partition by usr_id order by login_time)
) as dt
from (select usr_id,date(login_time) as login_time fromuser_login_logwherelogin_time>='2024-01-01'group by usr_id,date(login_time) order by usr_id,date(login_time)) t
)
selectusr_id,login_time ,dt,(case 
when dt>= 3 then "h"
when dt<= 3 then "l"
else "x" end) as dn
fromt
) t2
group by dn,login_time order by login_time ,dn
2024-12-25 按终点统计行程次数  
select end_loc,count(*) as cnt from hello_bike_riding_rcd group by end_loc order by count(*) desc
2024-12-25 Halo出行-通勤活跃用户标签开发  
withtas(
	select r.user_id,
		DATE_FORMAT(r.start_time, '%Y-%m') AS m_y,
		COUNT(*) AS c_d 
	from hello_bike_riding_rcd r 
		join gd_loc_mapm1 on r.start_loc =m1.loc_nm
		join gd_loc_mapm2 on r.end_loc = m2.loc_nm
	where (
			( m1.loc_type='写字楼' and m2.loc_type='地铁站')or
			( m2.loc_type='写字楼' and m1.loc_type='地铁站')
		)
		AND r.start_time BETWEEN '2020-01-01' AND '2024-12-31'
	group by r.user_id,date_format(r.start_time, '%Y-%m')
)
select
	user_id,
	case 
		when count( distinct m_y)>=3 and 
		min(c_d)>=5
		then 1
		else 0
	end as active_tag
	from t
	GROUP BY user_id;