리텐션 - 리텐션 정확도 향상을 위한 시계열 조절

ID별 첫 번째 임기 시작 날짜

SELECT id_bioguide, MIN(term_start) AS first_term
FROM legislators_terms
GROUP BY id_bioguide;

ID별 임기 period 계산

• 위 쿼리를 서브쿼리로 사용한다.
• 위 쿼리와 legislators_terms 테이블을 ID를 기준으로 JOIN 한다.
• JOIN한 테이블과 date_dim테이블을 JOIN 한다.
• date_dim 테이블의 날짜 컬럼을 임기 시작과 끝나는 날짜 사이를 기준으로 JOIN 한다.
• 그리고 date_dim 테이블이 December인 경우와 31일에 해당하는 경우만 JOIN 한다.

  SELECT
    A.id_bioguide,
    A.first_term,
    B.term_start,
    B.term_end,
    C.date,
    DATE_PART('year', AGE(C.date, A.first_term)) AS period
  FROM(
    SELECT id_bioguide, MIN(term_start) AS first_term
    FROM legislators_terms
    GROUP BY id_bioguide
  ) AS A JOIN legislators_terms AS B ON A.id_bioguide = B.id_bioguide
    LEFT JOIN date_dim AS C ON C.date between B.term_start AND B.term_end
        AND C.month_name='December' AND C.day_of_month=31;
  

대략적인 과정은 위에 작성한 것과 같지만, 보다 자세히 이해하기 위해 예시로 ID가 A000004인 경우를 살펴보자.

• A 테이블에서의 A000004 데이터는 다음과 같다.
• ID가 A000004에 해당하는 사람을 3번의 임기 기간이 존재한다.

• A와 B 테이블을 JOIN한 결과는 다음과 같다.

• C 테이블까지 JOIN한 결과는 다음과 같다.

date_dim 테이블의 date 컬럼에서 12월 31일인 경우의 데이터만을 가지고 date 날짜가 임기 시작일과 끝나는 날짜 사이를 기준으로 JOIN하였다. date - first_term을 해주면 period가 계산된다.

Period와 Cohort retained

• 위 쿼리에서 GROUP BY를 추가하고 SELECT절만 변경한다.

  SELECT
    COALESCE(DATE_PART('year', AGE(C.date, A.first_term)), 0) AS period,
    COUNT(DISTINCT A.id_bioguide) AS cohort_retained
  FROM(
    SELECT id_bioguide, MIN(term_start) AS first_term
    FROM legislators_terms
    GROUP BY id_bioguide
  ) AS A JOIN legislators_terms AS B ON A.id_bioguide = B.id_bioguide
    LEFT JOIN date_dim AS C ON C.date between B.term_start AND B.term_end
        AND C.month_name='December' AND C.day_of_month=31
  GROUP BY COALESCE(DATE_PART('year', AGE(C.date, A.first_term)), 0);
  

  

코호트 size와 period별 비율

SELECT 
	period,
	cohort_retained,
	FIRST_VALUE(cohort_retained) OVER(ORDER BY period) AS cohort_size,
	ROUND(cohort_retained * 1.0 / FIRST_VALUE(cohort_retained) OVER(ORDER BY period), 2) AS pct_retained
FROM(
	SELECT coalesce(date_part('year',age(c.date,a.first_term)),0) as period
	,count(distinct a.id_bioguide) as cohort_retained
	FROM(
		SELECT id_bioguide, min(term_start) as first_term
		FROM legislators_terms
		GROUP BY id_bioguide
	) a
	JOIN legislators_terms b on a.id_bioguide = b.id_bioguide
	LEFT JOIN date_dim c on c.date between b.term_start and b.term_end
	and c.month_name = 'December' and c.day_of_month = 31
	GROUP BY COALESCE(DATE_PART('year', AGE(C.date, A.first_term)), 0)
) AS sub;

임기 type에 따른 term_end 계산

• 임기 타입이 rep일 경우 임기 시작 날짜 + 2년
• 임기 타입이 sen일 경우 임기 시작 날짜 + 6년

  SELECT
    A.id_bioguide,
    A.first_term,
    B.term_start,
    (CASE
     WHEN B.term_type='rep' THEN B.term_start + INTERVAL '2 years'
     WHEN B.term_type='sen' THEN B.term_start + INTERVAL '6 years'
     END) AS term_end
  FROM(
  SELECT id_bioguide, min(term_start) as first_term
  FROM legislators_terms
  GROUP BY id_bioguide
  ) AS A JOIN legislators_terms AS B On A.id_bioguide = B.id_bioguide;
  

  

LEAD() 함수를 사용한 term_end 계산

• LEAD() 함수를 사용하고, ID별로 term_start를 오름차순한다.
• 위에서 생성된 날짜에서 1일씩 뺀다.

  SELECT
    A.id_bioguide,
    A.first_term,
    B.term_start,
    LEAD(B.term_start) OVER(PARTITION BY A.id_bioguide ORDER BY term_start) - INTERVAL '1 day' AS term_end
  FROM(
  SELECT id_bioguide, min(term_start) as first_term
  FROM legislators_terms
  GROUP BY id_bioguide
  ) AS A JOIN legislators_terms AS B On A.id_bioguide = B.id_bioguide
  ORDER BY A.id_bioguide ASC, A.first_term ASC;