[SQL] rolling retention 계산

0. 목표

 - rolling retention 계산

1. 실습

 1) 데이터 만들기

  - 링크

 2) 쿼리 작성

  (1) 각 id 별 첫 번째 접속일과 마지막 접속일 연산

WITH summary AS (
	SELECT 
		id,
        MIN(dated) AS first_login,
        MAX(dated) AS last_login
	FROM sql_test.classic_retention
    GROUP BY 1
    ORDER BY 1
),

  (2) 달력 생성

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(first_login)) AS y FROM summary) AS d
    WHERE 
		CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY >= (SELECT MIN(first_login) FROM summary)
		AND CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY <= (SELECT MAX(last_login) FROM summary)
	ORDER BY dt ASC
),

  (3) 각 id 별 달력 생성

A AS (
	SELECT * 

	FROM summary

	INNER JOIN Calendar
	ON 1 = 1
		AND dt >= first_login
		AND dt <= last_login

	ORDER BY 1, 4 ASC
),

  (4) 일자별 숫자 세기

B AS (
	SELECT 
		dt,
		COUNT(id) AS cnt
	FROM A

	GROUP BY dt
	ORDER BY 1 ASC
),

  (5) rolling retention 계산

C AS (
	SELECT 
		dt,
		cnt,
		ROUND(cnt / (SELECT MAX(cnt) FROM B) * 100, 0) AS percent
	FROM B
    ORDER BY 1
)

2. 참고

 - 리텐션 계산하기 (2) Rolling Retention