[SQL] with, row_number 를 이용한 특정 방문 횟수 간의 기간 산정

0. 목표

 - 2회 방문과 3회 째 방문의 시간 차이

1. 실습

 1) 테이블 제작

SELECT 
	id, 
    Date, 
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date) AS rnum
FROM sql_test.with_test

 2) 데이터 삽입

INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(1, 1, '2021-01-01');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(2, 2, '2021-01-02');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(3, 2, '2021-01-03');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(4, 1, '2021-01-04');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(5, 3, '2021-01-05');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(6, 1, '2021-01-06');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(7, 3, '2021-01-07');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(8, 2, '2021-01-08');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(9, 4, '2021-01-09');
INSERT INTO sql_test.with_test(IndexId, id, Date) VALUES(10, 3, '2021-03-10');

 3) 데이터 확인

SELECT * FROM sql_test.with_test;

 4) row_number 를 통한 방문 횟수 산정

SELECT 
	id, 
    Date, 
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date) AS rnum
FROM sql_test.with_test;

 5) with 를 이용한 결과 도출

WITH A AS (
	SELECT 
		id, 
		Date, 
		ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date) AS rnum
	FROM sql_test.with_test
) 

SELECT 
	A.id, 
    A.Date, 
    A.rnum,
    B.Date,
    B.rnum,
    DATEDIFF(A.Date, B.Date)

FROM A

INNER JOIN A AS B
ON B.id = A.id
	AND B.rnum = 2

WHERE A.rnum = 3;