WITH MONTH_ACTIVE AS (
SELECT 
USER_ID, 
MONTH_TIMESTAMP, 
ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY MONTH_TIMESTAMP) AS RN 
FROM 
(
SELECT 
T1.USER_ID,
DATE_FORMAT(T1.START_TIME, '%Y-%m-01') AS MONTH_TIMESTAMP,
COUNT(DISTINCT DATE_FORMAT(T1.START_TIME, '%Y-%m-%d')) AS DAY_CNT
FROM 
hello_bike_riding_rcd T1 
LEFT JOIN gd_loc_map T2 ON T1.START_LOC = T2.LOC_NM
LEFT JOIN gd_loc_map T3 ON T1.START_LOC = T3.LOC_NM
WHERE 
DATE_FORMAT(T1.START_TIME, '%Y-%m-%d') BETWEEN '2020-01-01' AND '2024-12-31' 
AND T3.LOC_TYPE <> T2.LOC_TYPE 
AND T2.LOC_TYPE IN ('写字楼', '地铁站')
AND T3.LOC_TYPE IN ('写字楼', '地铁站')
GROUP BY 
T1.USER_ID, DATE_FORMAT(T1.START_TIME, '%Y-%m-01')
HAVING DAY_CNT >= 5
) T4
), 
ACTIVE_USER AS (
SELECT T1.USER_ID 
FROM MONTH_ACTIVE T1 
LEFT JOIN MONTH_ACTIVE T2 ON T1.USER_ID = T2.USER_ID AND T1.RN + 2 = T2.RN
WHERE DATEDIFF(STR_TO_DATE(T2.MONTH_TIMESTAMP, '%Y-%m-%d'), STR_TO_DATE(T1.MONTH_TIMESTAMP, '%Y-%m-%d')) = 2 
GROUP BY T1.USER_ID 
)
SELECT 
T1.USER_ID AS user_id,
CASE 
WHEN T2.USER_ID IS NULL THEN 0 
ELSE 1
END AS active_tag
FROM 
(SELECT USER_ID FROM hello_bike_riding_rcd GROUP BY USER_ID) T1 
LEFT JOIN ACTIVE_USER T2 ON T1.USER_ID = T2.USER_ID;

WITH MonthlyActive AS (
SELECT 
		T1.USER_ID,
		DATE_FORMAT(T1.START_TIME, '%Y-%m-01') AS MONTH_TIMESTAMP,
		COUNT(DISTINCT DATE_FORMAT(T1.START_TIME, '%Y-%m-%d')) AS DAY_CNT
	FROM 
		hello_bike_riding_rcd T1 
		LEFT JOIN gd_loc_map T2 ON T1.START_LOC = T2.LOC_NM
		LEFT JOIN gd_loc_map T3 ON T1.START_LOC = T3.LOC_NM
	WHERE 
		DATE_FORMAT(T1.START_TIME, '%Y-%m-%d') BETWEEN '2020-01-01' AND '2024-12-31' 
		AND T3.LOC_TYPE <> T2.LOC_TYPE 
		AND T2.LOC_TYPE IN ('写字楼', '地铁站')
		AND T3.LOC_TYPE IN ('写字楼', '地铁站')
	GROUP BY 
		T1.USER_ID, DATE_FORMAT(T1.START_TIME, '%Y-%m-01')
	HAVING DAY_CNT >= 5
),
ConsecutiveMonths AS (
SELECT 
user_id,
MONTH_TIMESTAMP,
LAG(MONTH_TIMESTAMP, 1) OVER (PARTITION BY user_id ORDER BY MONTH_TIMESTAMP) AS prev_month1,
LAG(MONTH_TIMESTAMP, 2) OVER (PARTITION BY user_id ORDER BY MONTH_TIMESTAMP) AS prev_month2
FROM 
MonthlyActive
)
SELECT 
	DISTINCT T.user_id
	, CASE 
		WHEN TT.USER_ID IS NOT NULL THEN 1 
		ELSE 0 
	END AS active_tag
FROM hello_bike_riding_rcd T 
	LEFT JOIN 
	(
		SELECT 
			user_id
		FROM 
			ConsecutiveMonths
		WHERE 
			(
				(MONTH(MONTH_TIMESTAMP) - MONTH(prev_month1) = 1 AND MONTH(prev_month1) - MONTH(prev_month2) = 1) 
				OR 
				(MONTH(MONTH_TIMESTAMP) - MONTH(prev_month1) = 1 AND prev_month2 IS NULL) 
				OR 
				(prev_month1 IS NULL AND prev_month2 IS NULL)
			)
		GROUP BY 
			user_id
	) TT ON T.USER_ID = TT.USER_ID

select distinct user_id 
	,0 as active_tag
from hello_bike_riding_rcd T1
WHERE DATE_FORMAT(T1.START_TIME, '%Y-%m-%d') BETWEEN '2020-01-01' AND '2024-12-31'

select distinct user_id 
	,0 as active_tag
from hello_bike_riding_rcd T1
WHERE DATE_FORMAT(T1.START_TIME, '%Y-%m-%d') BETWEEN '2020-01-01' AND '2024-12-31'
limit 5