[SQL] classic retention 계산

0. 목표 

 - classic retention 계산

1. 실습

 1) 테이블 생성

CREATE TABLE sql_test.classic_retention
(
	id int,
    is_visited int,
    dated datetime
);

 2) 데이터 삽입

INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(1, 1, '2022-01-01 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(2, 1, '2022-01-01 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(3, 1, '2022-01-01 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(4, 1, '2022-01-01 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(5, 1, '2022-01-01 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(1, 1, '2022-01-02 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(2, 1, '2022-01-02 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(1, 1, '2022-01-03 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(3, 1, '2022-01-03 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(1, 1, '2022-01-04 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(2, 1, '2022-01-04 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(3, 1, '2022-01-04 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(1, 1, '2022-01-05 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(1, 1, '2022-01-06 00:00:00');
INSERT INTO sql_test.classic_retention(id, is_visited, dated) VALUES(4, 1, '2022-01-06 00:00:00');

 3) classic retention 계산

  (1) A 에서 일별 접속자 수 구하기

  (2) B 에서 일별 접속자 수 중 가장 큰 수 구하기(첫 숫자 가져오기 위함)

  (3) 첫 숫자 대비 현재 숫자로 retention 산출

WITH A AS (
	SELECT
		date(dated) AS day,
		COUNT(distinct id) AS cnt
	FROM sql_test.classic_retention
	GROUP BY 1
),

B AS (
	SELECT 
		MAX(cnt) AS max_cnt
	FROM A
)

SELECT 
	A.day,
    A.cnt,
    (SELECT max_cnt FROM B) AS max_cnt,
    ROUND(A.cnt / (SELECT max_cnt FROM B) * 100, 0) AS classic_retention
    
FROM A 

ORDER BY 1;

2. 참고

 - 리텐션 계산하기 (1) Classic Retention