[SQL] 이탈 회원 확인

0. 목표

 - 이탈 회원 확인

1. 실습

 1) 테이블 생성

CREATE TABLE sql_test.login_table
(
	id int,
    when_login datetime
);

 2) 데이터 삽입

INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-01-01 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-01-02 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(2, '2022-01-03 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-01-05 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-01-10 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(2, '2022-01-11 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-02-04 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-02-04 01:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(2, '2022-02-04 00:00:00');
INSERT INTO sql_test.login_table(id, when_login) VALUES(1, '2022-02-05 00:00:00');

 3) 쿼리 제작

-- id, 날짜별로 묶기
WITH A AS (
	SELECT
		id, 
        DATE(when_login) AS login_date
    FROM sql_test.login_table
    GROUP BY 1, 2
    ORDER BY 1, 2
),

-- 달력 만들기, 시작일은 A 테이블에서 가장 빠른 날짜를 기준, 종료일은 현재 날짜
Calendar AS (
	SELECT CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY AS dt
	FROM
	 (SELECT 0 AS tt
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
		) AS tt
	, (SELECT 0 AS a
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
		) AS a
	 , (SELECT 0 AS b
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
		) AS b
	 , (SELECT 0 AS c
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
		) AS c
	 , (SELECT YEAR(MIN(login_date)) AS y FROM A) AS d
    WHERE 
		CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY >= (SELECT MIN(login_date) FROM A)
		AND CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY <= CURDATE()
	ORDER BY dt ASC
),

-- A 테이블을 id 기준으로 가장 빠른 접속일과 가장 늦은 접속일(필요없음) 추출
B AS (
	SELECT 
		id,
        MIN(login_date) AS min_login_date,
        MAX(login_date) AS max_login_date
	FROM A
    GROUP BY id
),

-- 달력과 데이터 연결(각 id 별 시작일 기준으로 거르기용)
C AS (
	SELECT 
		B.id,
        Calendar.dt,
        B.min_login_date,
        B.max_login_date
    
    FROM Calendar 
    
    LEFT JOIN B
    ON 1 = 1
		AND B.min_login_date <= Calendar.dt
        AND CURDATE() >= Calendar.dt
        
	ORDER BY 1, 2
),

-- A 테이블과 결합
D AS (
	SELECT 
		C.id,
        C.dt,
        A.login_date
    
    FROM C 
    
    LEFT JOIN A
    ON A.id = C.id
		AND C.dt = A.login_date
	
    ORDER BY 1, 2
),

-- id 별로 날짜가 들어있는 데이터 세기(이때 접속일이 중간에 끊긴다면 해당 숫자가 계속 유지됨)
E AS (
	SELECT 
		id,
		dt,
		login_date,
		COUNT(login_date) OVER(PARTITION BY id ORDER BY dt ASC) AS tmp

	FROM D

	ORDER BY 1, 2
),

-- id, tmp 기준으로 가장 빠른 로그인 날짜 연산
F AS (
	SELECT 
		id,
		dt,
		login_date,
		tmp,
		FIRST_VALUE(login_date) OVER(PARTITION BY id, tmp) AS standard_date

	FROM E

	ORDER BY 1, 2
),

-- standard_date 와 해당일(dt) 빼기
G AS (
	SELECT 
		id,
		dt,
		login_date,
		tmp,
		standard_date,
		DATEDIFF(dt, standard_date) AS result_date

	FROM F

	ORDER BY 1, 2
)

-- result 기준으로 이탈 유무 확인
SELECT 
	id,
    dt,
    login_date,
    tmp,
    result_date,
    CASE 
		WHEN result_date = 0 THEN '접속'
        WHEN result_date < 5 THEN '단기 미접속'
		WHEN result_date < 15 THEN '장기 미접속'
		ELSE '이탈'
	END AS status
	
FROM G

ORDER BY 1, 2;