全站第 74/1246 名

解决了 1/334 题

中等: 0/75
入门: 0/77
困难: 1/29
简单: 0/114
草履虫: 0/39

过去1年一共提交 155 次

Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug

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

竞赛：Halo出行-通勤活跃用户标签开发2025-06-30
达成1题里程碑2024-12-26

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m2.commute_month + 1
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m2.commute_month + 1
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
)
SELECT 
    u.user_id,
    CASE WHEN a.active_tag IS not NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN (
    SELECT DISTINCT m1.user_id,1 as active_tag
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m2.commute_month + 1
)a ON u.user_id = a.user_id
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m2.commute_month + 1
)a ON u.user_id = a.user_id
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
),
active_users AS (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m2.commute_month + 1 and m1.commute_days >= 5 and m2.commute_days >= 5 and m3.commute_days >= 5 
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
),
active_users AS (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m2.commute_month + 1 and m1.commute_days >= 5
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
SELECT DISTINCT user_id
FROM commute_records m1
WHERE EXISTS (
SELECT 1 FROM commute_records m2
WHERE m2.user_id = m1.user_id AND m2.commute_month = m1.commute_month + 1
AND EXISTS (
SELECT 1 FROM commute_records m3
WHERE m3.user_id = m2.user_id AND m3.commute_month = m2.commute_month + 1
)
)
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
on m2.user_id = m1.user_idand m2.commute_month = m1.commute_month + 1
    JOIN commute_records m3 
    on m3.user_id = m2.user_id and
    m3.commute_month = m2.commute_month + 1
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') AS commute_month,
        COUNT(DISTINCT DATE(r.start_time)) AS commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
        (
            (r.start_loc IN ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
            AND r.end_loc IN ('将台西','望京','望京南','阜通')) 
            OR
            (r.start_loc IN ('将台西','望京','望京南','阜通') 
            AND r.end_loc IN ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际商务园','西门子大厦'))
        ) 
        AND r.start_time >= '2020-01-01'
    GROUP BY r.user_id, commute_month
    HAVING commute_days >= 5
),
active_users AS (
    SELECT 
        m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
        ON m2.user_id = m1.user_id 
        AND m2.commute_month = DATE_FORMAT(DATE_ADD(STR_TO_DATE(m1.commute_month, '%Y%m'), INTERVAL 1 MONTH), '%Y%m')
    JOIN commute_records m3 
        ON m3.user_id = m1.user_id 
        AND m3.commute_month = DATE_FORMAT(DATE_ADD(STR_TO_DATE(m2.commute_month, '%Y%m'), INTERVAL 1 MONTH), '%Y%m')
    GROUP BY m1.user_id
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id;
2025-01-02 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(DISTINCT commute_days )) AS rn
    FROM (SELECT * from hello_bike_riding_rcd wherestart_time >= '2020-01-01') r
    JOIN (
        SELECT 
            loc_nm,
            CASE 
                WHEN loc_nm IN ('将台西') THEN '地铁站'
                WHEN loc_nm IN ('北京机床研究所') THEN '写字楼'
                ELSE loc_type 
            END as loc_type
        FROM gd_loc_map
        WHERE loc_type IN ('地铁站', '写字楼') 
    ) start_loc ON r.start_loc = start_loc.loc_nm
    JOIN (
        SELECT 
            loc_nm,
            CASE 
                WHEN loc_nm IN ('将台西') THEN '地铁站'
                WHEN loc_nm IN ('北京机床研究所') THEN '写字楼'
                ELSE loc_type 
            END as loc_type
        FROM gd_loc_map
        WHERE loc_type IN ('地铁站', '写字楼')
    ) end_loc ON r.end_loc = end_loc.loc_nm
    WHERE 
 start_loc.loc_type != end_loc.loc_type 
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
SELECT DISTINCT s1.user_id
FROM commute_records s1 
JOIN commute_records s3 
where s1.user_id = s3.user_id 
and s1.rn+2=s3.rn and s1.commute_month+2=s3.commute_month)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM hello_bike_riding_rcd
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2024-12-31 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
    SELECT 
        r.user_id,
        DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days
    FROM hello_bike_riding_rcd r
    WHERE 
((r.start_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦') 
and end_loc in ('将台西','望京','望京南','阜通')) 
 or(r.start_loc in ('将台西','望京','望京南','阜通')) and end_loc in ('中关村电子城','凯德广场','北京机床研究所','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) 
AND start_time >= '2020-01-01'
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
    SELECT DISTINCT m1.user_id
    FROM commute_records m1
    JOIN commute_records m2 
    JOIN commute_records m3 
    WHERE m2.user_id = m1.user_id 
    AND m3.user_id = m1.user_id
    AND m2.commute_month = m1.commute_month + 1
    AND m3.commute_month = m1.commute_month + 2
)
SELECT 
    u.user_id,
    CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
    SELECT DISTINCT user_id 
    FROM commute_records
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2024-12-31 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as commute_month,
COUNT(DISTINCT DATE(r.start_time) ) as commute_days,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(DISTINCT commute_days )) AS rn
FROM (SELECT * from hello_bike_riding_rcd wherestart_time >= '2020-01-01') r
JOIN (
SELECT 
loc_nm,
CASE 
WHEN loc_nm IN ('将台西') THEN '地铁站'
WHEN loc_nm IN ('北京机床研究所') THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
) start_loc ON r.start_loc = start_loc.loc_nm
JOIN (
SELECT 
loc_nm,
CASE 
WHEN loc_nm IN ('将台西') THEN '地铁站'
WHEN loc_nm IN ('北京机床研究所') THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼')
) end_loc ON r.end_loc = end_loc.loc_nm
WHERE 
 start_loc.loc_type != end_loc.loc_type 
GROUP BY user_id, commute_month
 HAVING commute_days >= 5
),
active_users AS (
SELECT DISTINCT s1.user_id
FROM commute_records s1 
JOIN commute_records s3 
where s1.user_id = s3.user_id 
and s1.rn+2=s3.rn and s1.commute_month+2=s3.commute_month)
SELECT 
u.user_id,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as active_tag
FROM (
SELECT DISTINCT user_id 
FROM hello_bike_riding_rcd
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;