排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-25 Halo出行-通勤活跃用户标签开发  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-27 小宇宙电台的同期群分析  
日期在动是什么意思呀
 
日期是动态生成的
2024-12-25 Halo出行-通勤活跃用户标签开发  
输出示例的数据是对的吗
 
那必须呀，都3个满分了

提交记录

提交日期 题目名称 提交代码
2024-12-30 Halo出行-通勤活跃用户标签开发  
with 
cte1 as (
select 
loc_nm,
coalesce(
case loc_nm
when '北京机床研究所' then '写字楼'
when '将台西' then '地铁站'
else loc_type
end,
loc_type
) as loc_type
from gd_loc_map
),
cte2 as (
select 
t1.user_id, 
date_format(t1.start_time, '%Y-%m-01') as YM, 
count(distinct date_format(t1.start_time, '%Y-%m-%d')) as date_cnt
from hello_bike_riding_rcd t1
join cte1 t2 on t1.start_loc = t2.loc_nm
join cte1 t3 on t1.end_loc = t3.loc_nm
where year(t1.start_time) between 2020 and 2024
and t2.loc_type <> t3.loc_type
and t2.loc_type in ('写字楼', '地铁站')
and t3.loc_type in ('写字楼', '地铁站')
group by 1,2
),
cte3 as (
select 
user_id
from (
select 
user_id, 
date_sub(YM, interval row_number() over (partition by user_id order by YM) month) as YM1
from (select user_id,YM from cte2 where date_cnt>=5) a
) grouped_data
group by user_id, YM1
having count(1) >= 3
)
select 
a.user_id,
case when b.user_id is not null then 1 else 0 end as active_tag
from (
select distinct user_id from hello_bike_riding_rcd
) a
left join cte3 b on a.user_id = b.user_id;
2024-12-27 小宇宙电台的同期群分析  
with cte1 as
(select distinct usr_id
	,date_format(login_time,'%Y-%m-%d') login_date
 	,min(date_format(login_time,'%Y-%m-%d')) over(partition by usr_id) first_date
from user_login_log),
cte2 as
(select distinct t1.usr_id
	,t1.login_date
 	,t1.first_date
 	,max(coalesce(t2.login_date,t1.login_date)) last_date
from cte1 t1 
left join cte1 t2 
on t1.usr_id=t2.usr_id and t2.login_date<t1.login_date
group by 1,2,3),
cte3 as
(select usr_id
	,date_sub(login_date,interval 2 day) login_date
,case when login_date=first_date then '新增用户'
	when datediff(login_date,last_date) between 1 and 3 then '留存用户'
else '回流用户' end as usr_type
from cte2 
where year(date_sub(login_date,interval 2 day))=2024)
select login_date
	,concat(round(count(distinct (case when usr_type='新增用户' then usr_id end))/count(distinct usr_id)*100,2),', ',round(count(distinct (case when usr_type='留存用户' then usr_id end))/count(distinct usr_id)*100,2),', ',round(count(distinct (case when usr_type='回流用户' then usr_id end))/count(distinct usr_id)*100,2)) pct
from cte3
group by login_date
order by login_date
2024-12-27 小宇宙电台的同期群分析  
with cte1 as
(select distinct usr_id
	,date_format(login_time,'%Y-%m-%d') login_date
 	,min(date_format(login_time,'%Y-%m-%d')) over(partition by usr_id) first_date
from user_login_log),
cte2 as
(select distinct t1.usr_id
	,t1.login_date
 	,t1.first_date
 	,max(coalesce(t2.login_date,t1.login_date)) last_date
from cte1 t1 
left join cte1 t2 
on t1.usr_id=t2.usr_id and t2.login_date<t1.login_date
group by 1,2,3),
cte3 as
(select usr_id
	,login_date
,case when login_date=first_date then '新增用户'
	when datediff(login_date,last_date) between 1 and 3 then '留存用户'
else '回流用户' end as usr_type
from cte2 
where year(login_date)=2024)
select login_date
	,concat(round(count(distinct (case when usr_type='新增用户' then usr_id end))/count(distinct usr_id)*100,2),', ',round(count(distinct (case when usr_type='留存用户' then usr_id end))/count(distinct usr_id)*100,2),', ',round(count(distinct (case when usr_type='回流用户' then usr_id end))/count(distinct usr_id)*100,2)) pct
from cte3
group by login_date
order by login_date
2024-12-27 Halo出行-通勤活跃用户标签开发  
with 
cte1 as (
select 
loc_nm,
coalesce(
case loc_nm
when '北京机床研究所' then '写字楼'
when '将台西' then '地铁站'
else loc_type
end,
loc_type
) as loc_type
from gd_loc_map
),
cte2 as (
select 
distinct user_id, 
date_format(start_time, '%Y-%m-01') as YM, 
date_format(start_time, '%Y-%m-%d') as start_date
from hello_bike_riding_rcd t1
join cte1 t2 on t1.start_loc = t2.loc_nm
join cte1 t3 on t1.end_loc = t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type <> t3.loc_type
and t2.loc_type in ('写字楼', '地铁站')
and t3.loc_type in ('写字楼', '地铁站')
),
cte3 as (
select 
user_id, 
YM, 
count(distinct start_date) as cnt
from cte2
group by user_id, YM
having cnt >= 5
),
cte4 as (
select 
user_id
from (
select 
user_id, 
date_sub(YM, interval row_number() over (partition by user_id order by YM) month) as YM1
from cte3
) subquery
group by user_id, YM1
having count(user_id) >= 3
)
select 
a.user_id,
case when b.user_id is not null then 1 else 0 end as active_tag
from (
select distinct user_id from hello_bike_riding_rcd
) a
left join cte4 b on a.user_id = b.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,concat(date_format(start_time,"%Y-%m"),'-01') YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 ,date_sub(YM,interval row_number() over(partition by user_id order by YM) month) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case 
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,concat(date_format(start_time,"%Y-%m"),'-01') YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 ,date_sub(YM,interval row_number() over(partition by user_id order by YM) month) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,concat(date_format(start_time,"%Y-%m"),'-01') YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 	 ,YM
 ,date_sub(YM,interval row_number() over(partition by user_id order by YM) month) YM1
from cte3 a) b
group by user_id,YM1
having count(distinct YM)>=3
)
select distinct user_id
, 1 as active_tag
from cte4
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,concat(date_format(start_time,"%Y-%m"),'-01') YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 	 ,YM
 ,date_sub(YM,interval row_number() over(partition by user_id order by YM) month) YM1
from cte3 a) b
group by user_id,YM1
having count(distinct YM)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
order by user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,concat(date_format(start_time,"%Y-%m"),'-01') YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 	 ,YM
 ,date_sub(YM,interval row_number() over(partition by user_id order by YM) month) YM1
from cte3 a) b
group by user_id,YM1
having count(distinct YM)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,concat(date_format(start_time,"%Y-%m"),'-01') YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 ,date_sub(YM,interval row_number() over(partition by user_id order by YM) month) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,date_format(start_time,"%Y%m") YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select user_id
 ,YM-row_number() over(partition by user_id order by YM) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,date_format(start_time,"%Y%m") YM
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select *
,YM-row_number() over(partition by user_id order by YM) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-26 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select distinct user_id 
		,date_format(start_time,"%Y-%m-%d") start_date
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,date_format(start_date,"%Y%m") YM
,count(distinct start_date) cnt
from cte2 a
group by 1,2),
cte4 as
(select distinct user_id
from 
(select *
,row_number() over(partition by user_id order by YM) rnk
,YM-row_number() over(partition by user_id order by YM) YM1
from (select * from cte3 where cnt>=5) a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select t1.*
,t2.loc_type start_loc_type
,t3.loc_type end_loc_type
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,date_format(start_date,"%Y%m") YM
,count(distinct start_date) cnt
from (select distinct user_id,date_format(start_time,"%Y-%m-%d") start_date from cte2) a
group by 1,2),
cte4 as
(select distinct user_id
from 
(select *
,row_number() over(partition by user_id order by YM) rnk
,YM-row_number() over(partition by user_id order by YM) YM1
from (select * from cte3 where cnt>=5) a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select t1.*
,t2.loc_type start_loc_type
,t3.loc_type end_loc_type
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select distinct user_id
,date_format(start_time,"%Y%m") YM
,count(distinct date_format(start_time,"%Y-%m-%d")) cnt
from cte2 a
group by 1,2
having count(distinct date_format(start_time,"%Y-%m-%d"))>=5),
cte4 as
(select distinct user_id
from 
(select *
,row_number() over(partition by user_id order by YM) rnk
,YM-row_number() over(partition by user_id order by YM) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select t1.*
,t2.loc_type start_loc_type
,t3.loc_type end_loc_type
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,date_format(start_time,"%Y%m") YM
,count(distinct date_format(start_time,"%Y-%m-%d")) cnt
from cte2 a
group by 1,2
having count(distinct date_format(start_time,"%Y-%m-%d"))>=5),
cte4 as
(select distinct user_id
from 
(select *
,row_number() over(partition by user_id order by YM) rnk
,YM-row_number() over(partition by user_id order by YM) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id
2024-12-25 Halo出行-通勤活跃用户标签开发  
with cte1 as(
select loc_nm
,case when loc_nm='北京机床研究所' then '写字楼'
when loc_nm='凯德广场' then '购物广场'
when loc_nm='将台西' then '地铁站'
 else loc_type
 end as loc_type
from gd_loc_map
),
cte2 as(
select t1.*
,t2.loc_type start_loc_type
,t3.loc_type end_loc_type
from hello_bike_riding_rcd t1 
left join cte1 t2 on t1.start_loc=t2.loc_nm
left join cte1 t3 on t1.end_loc=t3.loc_nm
where year(start_time) between 2020 and 2024
and t2.loc_type in ('写字楼','地铁站')
and t3.loc_type in ('写字楼','地铁站')
and t2.loc_type<>t3.loc_type
),
cte3 as(
select user_id
,date_format(start_date,"%Y%m") YM
,count(distinct start_date) cnt
from (select distinct user_id,date_format(start_time,"%Y-%m-%d") start_date from cte2) a
group by 1,2
having count(distinct start_date)>=5),
cte4 as
(select distinct user_id
from 
(select *
,row_number() over(partition by user_id order by YM) rnk
,YM-row_number() over(partition by user_id order by YM) YM1
from cte3 a) b
group by user_id,YM1
having count(user_id)>=3
)
select distinct a.user_id
,case when b.user_id is null then 0 else 1 end as active_tag
from (select distinct user_id from hello_bike_riding_rcd) a left join cte4 b
on a.user_id=b.user_id