排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE	when loc_nm='将台西' then '地铁站'
 when loc_nm='北京机床研究所' then '写字楼'
			else loc_type
END AS loc_type
FROM gd_loc_map
where loc_type = '地铁站' or loc_type = '写字楼' 
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12 + month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where (end_loc_map.loc_type='地铁站' and start_loc_map.loc_type='写字楼') or 
	(end_loc_map.loc_type='写字楼' and start_loc_map.loc_type='地铁站')
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE	when loc_nm='将台西' then '地铁站'
 when loc_nm='北京机床研究所' then '写字楼'
			else loc_type
END AS loc_type
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12 + month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
left JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
left JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where (end_loc_map.loc_type='地铁站' and start_loc_map.loc_type='写字楼') or 
	(end_loc_map.loc_type='写字楼' and start_loc_map.loc_type='地铁站')
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='北京机床研究所' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 2
			when loc_nm='方恒购物中心' then 2
			when loc_nm='北京机床研究所' then 0
			when loc_nm='将台西' then 1 
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='北京机床研究所' then 0
			when loc_nm='将台西' then 1 
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 0
			when loc_nm='凯德广场' then 2
			when loc_nm='天启大厦' then 2
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='北京机床研究所' then 0
			when loc_nm='将台西' then 1 
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='天启大厦' then 2
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='北京机床研究所' then 0
			when loc_nm='将台西' then 1 
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 0
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 2
			when loc_nm='方恒购物中心' then 2
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 0
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 2
			when loc_nm='方恒购物中心' then 2
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 0
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 0
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-30 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 0
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 2
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 0
			when loc_nm='北京机床研究所' then 2
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59' 
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
 SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 0
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 1
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1 
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59' 
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num = 3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE	when loc_nm='将台西' then '地铁站'
			else loc_type
END AS loc_type
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
left JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
left JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where (end_loc_map.loc_type='地铁站' and start_loc_map.loc_type='写字楼') or 
	(end_loc_map.loc_type='写字楼' and start_loc_map.loc_type='地铁站')
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
		loc_type,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 1
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1
			when loc_nm='恒通国际商务园' then 0
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num=3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 1
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1
			when loc_nm='恒通国际商务园' then 0
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num=3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 1
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 2
			when loc_nm='方恒购物中心' then 2
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num=3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;
2024-12-27 Halo出行-通勤活跃用户标签开发  
WITH filtered_gd_loc_map AS (
SELECT
loc_nm as location,
 CASE when loc_nm='中关村电子城' then 2
			when loc_nm='凯德广场' then 2
			when loc_nm='北京机床研究所' then 1
			when loc_nm='天启大厦' then 2
			when loc_nm='将台西' then 1
			when loc_nm='恒通国际商务园' then 2
			when loc_nm='新世界百货' then 0
			when loc_nm='方恒购物中心' then 0
			when loc_nm='望京' then 1
			when loc_nm='望京南' then 1
			when loc_nm='瀚海国际大厦' then 2
			when loc_nm='西门子大厦' then 2
			when loc_nm='阜通' then 1
ELSE 0
END AS location_class_num
FROM gd_loc_map
),
filtered_rides AS (
SELECT
user_id,
date_format(start_time,'%Y-%m-%d') as start_date,
year(start_time)*12+month(start_time) AS start_month,
start_loc,
end_loc
FROM hello_bike_riding_rcd
WHERE start_time BETWEEN '2020-01-01 00:00:00' AND '2024-12-31 23:59:59'
),
riding_data AS (
SELECT
fr.user_id,
fr.start_month AS months,
row_number() over(partition by fr.user_id order by fr.start_month asc) as rn
FROM filtered_rides fr
JOIN filtered_gd_loc_map start_loc_map
ON fr.start_loc = start_loc_map.location
JOIN filtered_gd_loc_map end_loc_map
ON fr.end_loc = end_loc_map.location
where end_loc_map.location_class_num + start_loc_map.location_class_num=3
GROUP BY fr.user_id, fr.start_month
having count(distinct fr.start_date)>=5
) 
,
active_users as ( 
 select user_id
 from riding_data
 group by user_id,months - rn 
 having count(*)>=3)
SELECT
all_users.user_id,
CASE
WHEN active_users.user_id IS NOT NULL THEN 1
ELSE 0
END AS active_tag
FROM (SELECT DISTINCT user_id FROM filtered_rides) AS all_users
LEFT JOIN (select DISTINCT user_id from active_users) as active_users ON all_users.user_id = active_users.user_id
order by all_users.user_id;