排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
),
commute_records AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key,
COUNT(DISTINCT DATE(r.start_time)) as days
FROM (
SELECT * 
FROM hello_bike_riding_rcd 
WHERE start_time >= '2020-01-01'
) r
INNER JOIN loc_types s ON r.start_loc = s.loc_nm
INNER JOIN loc_types e ON r.end_loc = e.loc_nm
WHERE ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM commute_records m1
JOIN commute_records m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN commute_records m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT 
DISTINCT r.user_id,
IF(a.user_id IS NOT NULL, 1, 0) as active_tag
FROM (
SELECT DISTINCT user_id 
FROM hello_bike_riding_rcd 
WHERE start_time >= '2020-01-01'
) r
LEFT JOIN active_users a ON r.user_id = a.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
),
commute_records AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key,
COUNT(DISTINCT DATE(r.start_time)) as days
FROM (
SELECT * 
FROM hello_bike_riding_rcd 
WHERE start_time >= '2020-01-01'
) r
inner JOIN loc_types s ON r.start_loc = s.loc_nm
inner JOIN loc_types e ON r.end_loc = e.loc_nm
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM commute_records m1
JOIN commute_records m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN commute_records m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT 
DISTINCT r.user_id,
IF(a.user_id IS NOT NULL, 1, 0) as active_tag
FROM (
SELECT DISTINCT user_id 
FROM hello_bike_riding_rcd 
WHERE start_time >= '2020-01-01'
) r
LEFT JOIN active_users a ON r.user_id = a.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_rides AS (
SELECT *
FROM hello_bike_riding_rcd
WHERE start_time >= '2020-01-01'
AND start_loc IN (
SELECT loc_nm
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
)
AND end_loc IN (
SELECT loc_nm
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
)
),
loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
),
commute_records AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key,
COUNT(DISTINCT DATE(r.start_time)) as days
FROM filtered_rides r
JOIN loc_types s ON r.start_loc = s.loc_nm
JOIN loc_types e ON r.end_loc = e.loc_nm
WHERE ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM commute_records m1
JOIN commute_records m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN commute_records m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT 
DISTINCT r.user_id,
IF(a.user_id IS NOT NULL, 1, 0) as active_tag
FROM filtered_rides r
LEFT JOIN active_users a ON r.user_id = a.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
SELECT 
DISTINCT r.user_id,
IF(a.user_id IS NOT NULL, 1, 0) as active_tag
FROM hello_bike_riding_rcd r
LEFT JOIN (
SELECT DISTINCT m1.user_id
FROM (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key
FROM hello_bike_riding_rcd r
JOIN (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
) s ON r.start_loc = s.loc_nm
JOIN (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
) e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
AND ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE(r.start_time), DATE_FORMAT(r.start_time, '%Y%m')
HAVING COUNT(DISTINCT DATE(r.start_time)) >= 5
) m1
JOIN (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key
FROM hello_bike_riding_rcd r
JOIN (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
) s ON r.start_loc = s.loc_nm
JOIN (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
) e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
AND ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE(r.start_time), DATE_FORMAT(r.start_time, '%Y%m')
HAVING COUNT(DISTINCT DATE(r.start_time)) >= 5
) m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key
FROM hello_bike_riding_rcd r
JOIN (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
) s ON r.start_loc = s.loc_nm
JOIN (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
) e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
AND ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE(r.start_time), DATE_FORMAT(r.start_time, '%Y%m')
HAVING COUNT(DISTINCT DATE(r.start_time)) >= 5
) m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
) a ON r.user_id = a.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼') 
OR loc_nm IN ('将台西', '北京机床研究所')
),
commute_records AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key,
COUNT(DISTINCT DATE(r.start_time)) as days
FROM hello_bike_riding_rcd r
JOIN loc_types s ON r.start_loc = s.loc_nm
JOIN loc_types e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
AND ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM commute_records m1
JOIN commute_records m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN commute_records m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT 
DISTINCT r.user_id,
IF(a.user_id IS NOT NULL, 1, 0) as active_tag
FROM hello_bike_riding_rcd r
LEFT JOIN active_users a ON r.user_id = a.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH commute_stats AS (
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key,
COUNT(DISTINCT DATE(r.start_time)) as days
FROM hello_bike_riding_rcd r
JOIN (
SELECT loc_nm, '地铁站' as loc_type 
FROM gd_loc_map 
WHERE loc_type = '地铁站' OR loc_nm = '将台西'
) s ON r.start_loc = s.loc_nm
JOIN (
SELECT loc_nm, '写字楼' as loc_type 
FROM gd_loc_map 
WHERE loc_type = '写字楼' OR loc_nm = '北京机床研究所'
) e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING days >= 5
UNION ALL
SELECT 
r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key,
COUNT(DISTINCT DATE(r.start_time)) as days
FROM hello_bike_riding_rcd r
JOIN (
SELECT loc_nm, '写字楼' as loc_type 
FROM gd_loc_map 
WHERE loc_type = '写字楼' OR loc_nm = '北京机床研究所'
) s ON r.start_loc = s.loc_nm
JOIN (
SELECT loc_nm, '地铁站' as loc_type 
FROM gd_loc_map 
WHERE loc_type = '地铁站' OR loc_nm = '将台西'
) e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM commute_stats m1
JOIN commute_stats m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN commute_stats m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT 
DISTINCT r.user_id,
IF(a.user_id IS NOT NULL, 1, 0) as active_tag
FROM hello_bike_riding_rcd r
LEFT JOIN active_users a ON r.user_id = a.user_id;
2024-12-30 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
    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 
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 hello_bike_riding_rcd
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼')
OR loc_nm IN ('将台西', '北京机床研究所')
),
valid_rides AS (
SELECT r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key
FROM hello_bike_riding_rcd r
JOIN loc_types s ON r.start_loc = s.loc_nm
JOIN loc_types e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
AND ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE_FORMAT(r.start_time, '%Y%m')
HAVING COUNT(DISTINCT DATE(r.start_time)) >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM valid_rides m1
JOIN valid_rides m2 ON m1.user_id = m2.user_id 
AND m2.month_key = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(m1.month_key, '01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m')
JOIN valid_rides m3 ON m2.user_id = m3.user_id 
AND m3.month_key = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(m2.month_key, '01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m')
)
SELECT r.user_id,
MAX(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) r
LEFT JOIN active_users a ON r.user_id = a.user_id
GROUP BY r.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
WHERE loc_type IN ('地铁站', '写字楼')
OR loc_nm IN ('将台西', '北京机床研究所')
),
valid_rides AS (
SELECT r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key
FROM hello_bike_riding_rcd r
JOIN loc_types s ON r.start_loc = s.loc_nm
JOIN loc_types e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
AND ((s.loc_type = '地铁站' AND e.loc_type = '写字楼')
OR (s.loc_type = '写字楼' AND e.loc_type = '地铁站'))
GROUP BY r.user_id, DATE(r.start_time), DATE_FORMAT(r.start_time, '%Y%m')
),
monthly_rides AS (
SELECT user_id, month_key
FROM valid_rides
GROUP BY user_id, month_key
HAVING COUNT(*) >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM monthly_rides m1
JOIN monthly_rides m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN monthly_rides m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT r.user_id,
MAX(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) r
LEFT JOIN active_users a ON r.user_id = a.user_id
GROUP BY r.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH loc_types AS (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
),
valid_rides AS (
SELECT r.user_id,
DATE_FORMAT(r.start_time, '%Y%m') as month_key
FROM hello_bike_riding_rcd r
JOIN loc_types s ON r.start_loc = s.loc_nm
JOIN loc_types e ON r.end_loc = e.loc_nm
WHERE r.start_time >= '2020-01-01'
GROUP BY r.user_id, DATE(r.start_time), DATE_FORMAT(r.start_time, '%Y%m')
),
monthly_rides AS (
SELECT user_id, month_key
FROM valid_rides
GROUP BY user_id, month_key
HAVING COUNT(*) >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM monthly_rides m1
JOIN monthly_rides m2 ON m1.user_id = m2.user_id 
AND m2.month_key = m1.month_key + 1
JOIN monthly_rides m3 ON m2.user_id = m3.user_id 
AND m3.month_key = m2.month_key + 1
)
SELECT r.user_id,
MAX(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) r
LEFT JOIN active_users a ON r.user_id = a.user_id
GROUP BY r.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
SELECT 
r.user_id,
DATE(r.start_time) as commute_date,
DATE_FORMAT(r.start_time, '%Y%m') as commute_month
FROM hello_bike_riding_rcd r
JOIN (
SELECT loc_nm, loc_type
FROM (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
) t
WHERE loc_type IN ('地铁站', '写字楼')
) start_loc ON r.start_loc = start_loc.loc_nm
JOIN (
SELECT loc_nm, loc_type
FROM (
SELECT loc_nm,
CASE 
WHEN loc_nm = '将台西' THEN '地铁站'
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END as loc_type
FROM gd_loc_map
) t
WHERE loc_type IN ('地铁站', '写字楼')
) end_loc ON r.end_loc = end_loc.loc_nm
WHERE r.start_time >= '2020-01-01'
AND (
(start_loc.loc_type = '地铁站' AND end_loc.loc_type = '写字楼')
OR 
(start_loc.loc_type = '写字楼' AND end_loc.loc_type = '地铁站')
)
),
monthly_stats AS (
SELECT 
user_id,
commute_month,
COUNT(DISTINCT commute_date) as days
FROM commute_records
GROUP BY user_id, commute_month
HAVING days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM monthly_stats m1
JOIN monthly_stats m2 ON m1.user_id = m2.user_id AND m2.commute_month = m1.commute_month + 1
JOIN monthly_stats m3 ON m2.user_id = m3.user_id AND m3.commute_month = m2.commute_month + 1
)
SELECT 
u.user_id,
MAX(CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END) as active_tag
FROM hello_bike_riding_rcd u
LEFT JOIN active_users a ON u.user_id = a.user_id
GROUP BY u.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH commute_records AS (
SELECT 
r.user_id,
DATE(r.start_time) as commute_date,
DATE_FORMAT(r.start_time, '%Y%m') as commute_month
FROM hello_bike_riding_rcd 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 ('地铁站', '写字楼') 
OR loc_nm 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 ('地铁站', '写字楼')
OR loc_nm IN ('将台西', '北京机床研究所')
) end_loc ON r.end_loc = end_loc.loc_nm
WHERE 
r.start_time BETWEEN '2020-01-01' AND CURRENT_DATE()
AND (
(start_loc.loc_type = '地铁站' AND end_loc.loc_type = '写字楼')
OR 
(start_loc.loc_type = '写字楼' AND end_loc.loc_type = '地铁站')
)
GROUP BY r.user_id, commute_date, commute_month
),
monthly_commute AS (
SELECT 
user_id,
commute_month,
COUNT(DISTINCT commute_date) as commute_days
FROM commute_records
GROUP BY user_id, commute_month
HAVING commute_days >= 5
),
active_users AS (
SELECT DISTINCT m1.user_id
FROM monthly_commute m1
JOIN monthly_commute m2 
JOIN monthly_commute 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 hello_bike_riding_rcd
) u
LEFT JOIN active_users a ON u.user_id = a.user_id
ORDER BY u.user_id;