排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-26 小宇宙电台的同期群分析  已解决
2024-12-25 Halo出行-通勤活跃用户标签开发  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-26 小宇宙电台的同期群分析  
为啥我的结果的日期和示例总是岔一天
 
日期在动，没关系的

提交记录

提交日期 题目名称 提交代码
2025-03-19 登录天数分布  
WITH user_login_days AS (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
    FROM 
        user_login_log
    WHERE 
        login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
	group by 1,2
)
,distinct_login_days AS (
    SELECT 
        usr_id,
        COUNT(1) AS login_days
    FROM 
        user_login_days
    GROUP BY 
        usr_id
)
SELECT 
    SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
    SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
    SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
    SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    distinct_login_days;
2025-03-19 登录天数分布  
WITH user_login_days AS (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
    FROM 
        user_login_log
    WHERE 
        login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
),
distinct_login_days AS (
    SELECT 
        usr_id,
        COUNT(DISTINCT login_date) AS login_days
    FROM 
        user_login_days
    GROUP BY 
        usr_id
)
SELECT 
    SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
    SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
    SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
    SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    distinct_login_days;
2025-03-19 登录天数分布  
WITH user_login_days AS (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
			,count(1) as login_days
    FROM 
        user_login_log
    WHERE 
        login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
	group by 1,2
)
SELECT 
    SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
    SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
    SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
    SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    user_login_days;