排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-25 小宇宙电台的同期群分析  
终于可以了。。。
 
啥也没说
2024-12-25 小宇宙电台的同期群分析  
我也求一个答案。我提交的代码跑出来都是0分
 
啥也没说
2024-12-25 小宇宙电台的同期群分析  
示例里是真实结果吗？提交答案始终不对，也看不出来哪里不对。。
 
是真实的结果。
你再提交试试，刚看了你的答案应该可以得满分。

提交记录

提交日期 题目名称 提交代码
2025-04-22 天王天后的发烧友  
with main as (
select 
c.user_id
,date(c.start_time) as start_date
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
join listen_rcd c on b.song_id = c.song_id
where a.singer_id in (1,2,3,4,6)
 group by 1,2
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
start_date - INTERVAL seq DAY AS mark,
COUNT(*) as mark_cnt
from 
(
select 
user_id
,start_date
,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY start_date) as seq
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-22 天王天后的发烧友  
with main as (
select 
c.user_id
,date(c.start_time) as start_date
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
join listen_rcd c on b.song_id = c.song_id
where a.singer_id in (1,2,3,4,6)
 group by c.user_id
,start_date
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
start_date - INTERVAL seq DAY AS mark,
COUNT(*) as mark_cnt
from 
(
select 
user_id
,start_date
,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY start_date) as seq
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-22 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select 
a.user_id
,date(a.start_time) as start_date
from song c
join listen_rcd aon a.song_id = c.song_id
group by a.user_id
,date(a.start_time)
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
start_date - INTERVAL seq DAY AS mark,
COUNT(*) as mark_cnt
from 
(
select 
user_id
,start_date
,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY start_date) as seq
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-21 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
group by 1,2
)
,main as(
select 
a.user_id
,date(a.start_time) as start_date
from song c
join listen_rcd aon a.song_id = c.song_id
group by a.user_id
,date(a.start_time)
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
start_date - INTERVAL seq DAY AS mark,
COUNT(*) as mark_cnt
from 
(
select 
user_id
,start_date
,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY start_date) as seq
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-21 天王天后的发烧友  
with main as (
select 
c.user_id
,date(c.start_time) as start_date
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
join listen_rcd con b.song_id = c.song_id
where a.singer_id in (1,2,3,4,6)
group by 1,2
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
start_date - INTERVAL seq DAY AS mark,
COUNT(*) as mark_cnt
from 
(
select 
user_id
,start_date
,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY start_date) as seq
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6) and b.origin_singer_id in (1,2,3,4,6)
)
,main as(
select 
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
from song c
join listen_rcd aon a.song_id = c.song_id
group by a.user_id
,date_format(a.start_time,'%Y-%m-%d')
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select 
user_id
,start_date
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select 
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
from song c
join listen_rcd aon a.song_id = c.song_id
group by a.user_id
,date_format(a.start_time,'%Y-%m-%d')
)
select
user_id,
max(mark_cnt) max_consecutive_days
from 
(
select 
user_id,
mark,
row_number()over(partition by user_id,mark)as mark_cnt
from 
(
select 
user_id
,start_date
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select 
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
from song c
join listen_rcd aon a.song_id = c.song_id
group by a.user_id
,date_format(a.start_time,'%Y-%m-%d')
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select 
user_id
,start_date
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select 
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
from listen_rcd a 
join song c on a.song_id = c.song_id
group by a.user_id
,date_format(a.start_time,'%Y-%m-%d')
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select 
user_id
,start_date
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select 
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
group by a.user_id
,date_format(a.start_time,'%Y-%m-%d')
,a.song_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,dense_rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (dense_rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,7)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,5,7)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,5,6,7)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,5,6)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,5)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id
2025-04-14 天王天后的发烧友  
with song as (
select 
a.singer_id
,a.singer_name
,b.song_id
from singer_info a join song_info b
on a.singer_id = b.origin_singer_id
where a.singer_id in (1,2,3,4,6,7)
)
,main as(
select distinct
a.user_id
,date_format(a.start_time,'%Y-%m-%d') as start_date
,a.song_id
from listen_rcd a 
join song c on a.song_id = c.song_id
join qqmusic_user_info b
on a.user_id = b.user_id
)
select
user_id
,max(mark_cnt) asmax_consecutive_days
from
(
select 
user_id,
mark,
count(distinct start_date) as mark_cnt
from 
(
select distinct
user_id
,start_date
,rank()over(partition by user_id order by start_date) as r_num
,date_sub(start_date,interval (rank()over(partition by user_id order by start_date)) day) as mark
from main 
)main_2
group by user_id,mark
)main_3
group by user_id