排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id <='6'
 and t1.type3='个人'
)t1
on t.song_id=t1.song_id
 group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
)t1
on t.song_id=t1.song_id
 group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id <='6'
)t1
on t.song_id=t1.song_id
 group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id <='6'
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id in ('1','2','4','6','3')
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id in ('1','2','4','6')
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id<='6'
and t1.type3='个人'
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id<='6'
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
order by user_id asc
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id<='6'
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1
2025-04-15 天王天后的发烧友  
with listen_rcd_query as (
 select 
t.user_id
 ,date(t.start_time) as start_time
 from listen_rcd t
 inner join (
 select t2.song_id
 from singer_info t1
 inner join song_info t2
on t1.singer_id=t2.origin_singer_id
 where t1.singer_id<='6'
 and t1.type3='个人'
)t1
on t.song_id=t1.song_id
where if_finished='0'
group by 1,2
 )
select 
user_id
,max(cnt) as max_consecutive_days
from (
 select 
 user_id
 ,start_time_1
 ,count(1) as cnt
 from (
select 
 user_id
 ,start_time
 ,date_sub(start_time,interval rn day) asstart_time_1
from (
 select 
 user_id
 ,start_time
 ,row_number() over(partition by user_id order by start_time asc) as rn
 from listen_rcd_query
)t
 )t
group by 1,2
 ) t
group by 1