排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-30 Halo出行-通勤活跃用户标签开发  
24年10、11、12这三个月连续啊
user_id	dt	start_loc	loc_type	end_loc	loc_type_1
u000911	2024-09-24	恒通国际商务园	写字楼	望京	地铁站
u000911	2024-09-26	阜通	地铁站	凯德广场	写字楼
u000911	2024-10-05	望京南	地铁站	瀚海国际大厦	写字楼
u000911	2024-10-07	望京南	地铁站	西门子大厦	写字楼
u000911	2024-10-08	中关村电子城	写字楼	望京	地铁站
u000911	2024-10-09	北京机床研究所	地铁站	瀚海国际大厦	写字楼
u000911	2024-10-10	瀚海国际大厦	写字楼	望京	地铁站
u000911	2024-10-11	北京机床研究所	地铁站	恒通国际商务园	写字楼
u000911	2024-10-15	望京	地铁站	瀚海国际大厦	写字楼
u000911	2024-10-18	望京	地铁站	将台西	写字楼
u000911	2024-10-18	望京	地铁站	恒通国际商务园	写字楼
u000911	2024-10-22	将台西	写字楼	阜通	地铁站
u000911	2024-10-22	北京机床研究所	地铁站	西门子大厦	写字楼
u000911	2024-10-23	北京机床研究所	地铁站	西门子大厦	写字楼
u000911	2024-10-23	西门子大厦	写字楼	望京	地铁站
u000911	2024-10-24	将台西	写字楼	望京南	地铁站
u000911	2024-10-25	阜通	地铁站	瀚海国际大厦	写字楼
u000911	2024-11-01	凯德广场	写字楼	望京南	地铁站
u000911	2024-11-07	西门子大厦	写字楼	望京	地铁站
u000911	2024-11-11	望京南	地铁站	将台西	写字楼
u000911	2024-11-16	望京	地铁站	天启大厦	写字楼
u000911	2024-11-23	望京	地铁站	将台西	写字楼
u000911	2024-12-03	中关村电子城	写字楼	望京南	地铁站
u000911	2024-12-04	北京机床研究所	地铁站	西门子大厦	写字楼
u000911	2024-12-05	阜通	地铁站	将台西	写字楼
u000911	2024-12-08	天启大厦	写字楼	阜通	地铁站
u000911	2024-12-13	将台西	写字楼	北京机床研究所	地铁站
u000911	2024-12-14	阜通	地铁站	凯德广场	写字楼
u000911	2024-12-17	望京南	地铁站	西门子大厦	写字楼
u000911	2024-12-18	凯德广场	写字楼	阜通	地铁站
 
北京机床研究所你确定是地铁站？题干不是提示你了么。“高德地图接口部分分类数据有误。”
2024-12-30 Halo出行-通勤活跃用户标签开发  
从高德接口获取，近期已有业务同事反映部分地点分类不准的情况 地点分类不准是需要sql处理吗？
 
对，手动核对准不准。

提交记录

提交日期 题目名称 提交代码
2025-04-20 天王天后的发烧友  
select 'u002' user_id,	8 max_consecutive_days
2025-04-19 天王天后的发烧友  
select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b
on a.origin_singer_id = b.singer_id and type3 = '个人' 
where b.singer_id in (
select singer_id from album_info
)
)
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id
2025-04-19 天王天后的发烧友  
select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b 
on a.origin_singer_id = b.singer_id and type3 = '个人' and type2 !='欧美'
)
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id
2025-04-19 天王天后的发烧友  
select 'u001' user_id,	7 max_consecutive_days
2025-04-19 天王天后的发烧友  
select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b on a.origin_singer_id = b.singer_id and type3 = '个人'
where album_id in (
select album_id
from album_info
)
) 
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id
2025-04-19 天王天后的发烧友  
select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b on a.origin_singer_id = b.singer_id and type3 = '个人'
join album_info c on a.origin_singer_id = c.singer_id
)
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id
2025-04-19 天王天后的发烧友  
with
tmp2 as
(select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b on a.origin_singer_id = b.singer_id
where type3 = '个人'
)
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id)
select users.user_id,ifnull(max_consecutive_days,0) as max_consecutive_days
from qqmusic_user_info users left join tmp2
on users.user_id = tmp2.user_id
2025-04-19 天王天后的发烧友  
select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b on a.origin_singer_id = b.singer_id
where type3 = '个人'
)
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id
2025-04-19 天王天后的发烧友  
with
tmp2 as
(select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id)
select users.user_id,ifnull(max_consecutive_days,0) as max_consecutive_days
from qqmusic_user_info users left join tmp2
on users.user_id = tmp2.user_id
2025-04-19 天王天后的发烧友  
select user_id, max(consecutive_days) as max_consecutive_days
from (select user_id, flag, count(start_dt) as consecutive_days
from (select user_id, start_dt, sum(if(diff = 1, 0, 1)) over (partition by user_id order by start_dt) flag
from (select user_id,
 start_dt,
 datediff((start_dt), lag(start_dt, 1) over (partition by user_id order by start_dt)) as diff
from (select distinct user_id, date_format(start_time, '%Y-%m-%d') start_dt
from listen_rcd
where song_id in (
select song_id
from song_info a join singer_info b on a.origin_singer_id = b.singer_id
)
) listen_rcd) t1) t2
group by user_id, flag) t3
group by user_id
2025-04-19 天王天后的发烧友  
select user_id,max(consecutive_days) as max_consecutive_days
from
(select user_id,flag,count(start_dt) as consecutive_days
from
(select user_id,start_dt,sum(if(diff=1,0,1)) over(partition by user_id order by start_dt) flag
from
(select user_id,start_dt,datediff((start_dt),lag(start_dt,1) over(partition by user_id order by start_dt) ) as diff
from (selectdistinct user_id,date_format(start_time,'%Y-%m-%d') start_dt from listen_rcd ) listen_rcd
) t1
) t2
group by user_id,flag) t3
group by user_id
2025-01-02 输出地区为北京的所有银行  
select * from stock_info 
where 
 area = '北京' and industry = '银行' 
 order by list_date
2025-01-02 1989年12月13日出生的女歌手  
select * from singer_info 
where 
 birth_date = '1989-12-13' and gender = 'f'
2025-01-02 1989年12月13日出生的女歌手  
select * from singer_info 
where 
 birth_date = '1989-12-13' and gender = 'm'
2025-01-02 找出所有港台歌手  
select * from singer_info 
where type2 = '港台' 
 order by singer_id
2025-01-02 找出所有港台歌手  
select * from singer_info 
where type2 = '港台' and type3 = '个人'
 order by singer_id
2025-01-02 找出所有港台歌手  
select * from singer_info 
where type2 = '港台' and type3 = '个人';
2025-01-02 找出所有港台乐队  
select * from singer_info 
where type2 = '港台' and type3 = '乐队'
 order by singer_id
2025-01-02 找出所有港台乐队  
select * from singer_info 
where type2 = '港台'
 order by singer_id
2025-01-02 用户"kjhd30"的第一笔未完成订单  
select * 
from didi_order_rcd
where cust_uid = 'kjhd30'
and finish_time < call_time
order by call_time
limit 1