排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-04-23 天王天后的发烧友  
WITH user_jay_songs AS (
    SELECT DISTINCT 
        l.user_id, 
        DATE(l.start_time) AS listen_date
    FROM listen_rcd l
    JOIN song_info s ON l.song_id = s.song_id
    WHERE s.origin_singer_id in (1,2,3,4,6)  
),
date_groups AS (
    SELECT 
        user_id,
        listen_date,
        DATE_SUB(listen_date, INTERVAL ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY listen_date
        ) DAY) AS grp  
    FROM user_jay_songs
),
group_counts AS (
    SELECT 
        user_id,
        grp,
        COUNT(*) AS consecutive_days
    FROM date_groups
    GROUP BY user_id, grp
),
max_counts AS (
    SELECT 
        user_id,
        MAX(consecutive_days) AS max_consecutive_days
    FROM group_counts
    GROUP BY user_id
)
SELECT 
    u.user_id,
    COALESCE(m.max_consecutive_days, 0) AS max_consecutive_days
FROM qqmusic_user_info u
LEFT JOIN max_counts m ON u.user_id = m.user_id;
2025-04-22 天王天后的发烧友  
WITH user_jay_songs AS (
    SELECT DISTINCT 
        l.user_id, 
        DATE(l.start_time) AS listen_date
    FROM listen_rcd l
    JOIN song_info s ON l.song_id = s.song_id
    WHERE s.origin_singer_id in (1,2,3,4,6)  
),
date_groups AS (
    SELECT 
        user_id,
        listen_date,
        DATE_SUB(listen_date, INTERVAL ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY listen_date
        ) DAY) AS grp  
    FROM user_jay_songs
),
group_counts AS (
    SELECT 
        user_id,
        grp,
        COUNT(*) AS consecutive_days
    FROM date_groups
    GROUP BY user_id, grp
),
max_counts AS (
    SELECT 
        user_id,
        MAX(consecutive_days) AS max_consecutive_days
    FROM group_counts
    GROUP BY user_id
)
SELECT 
    u.user_id,
    COALESCE(m.max_consecutive_days, 0) AS max_consecutive_days
FROM qqmusic_user_info u
LEFT JOIN max_counts m ON u.user_id = m.user_id;