1. 임기 종료 날짜를 활용한 리텐션 정확도 향상

1-1. 배경

※ 예제 데이터 및 이전 자료 참고
예제 데이터 출처
[SQL 코호트 분석] 2. 리텐션 분석 실습 (1)

코호트 분석에서 시계열 데이터를 다룰 때, 다음을 확인해야 한다.

• 데이터가 잘 저장되어 있는지
• 데이터가 현재를 정확하게 반영하는지
• 각 시간 구간에서 손실된 개체는 없는지

입법가 데이터셋에서 의원의 임기 시작 날짜는 있지만, 2년 또는 6년과 같이 의원직을 수행하는 "기간"에 대한 데이터가 손실된 상태이다.
실제로 "재임한 기간"에 대한 리텐션 분석의 정확도를 높인다.

12월 31일에 재임 중이라면 "해당 연도에 재임 중인 의원"으로 간주한다.
임기 시작을 의미하는 term_start 필드는 대부분 1월에 몰려 있지만, 다른 날짜들도 있다. 따라서, 각기 다른 임기 시작 날짜를 정규화하기 위해 12월 31일을 기준으로 판단할 것이다.

---

1-2. 12월 31일에 재임 중인 입법가 데이터셋 생성

먼저, 날짜 차원 테이블을 생성한다. 이는 다음과 같은 장점을 갖는다.

• 날짜 데이터 표준화: 날짜의 다양한 형태(연도, 월, 분기, 요일 등)으로 변환해 저장해 일관된 분석 기준을 제공한다.
• 복잡한 계산 없이 즉시 사용 가능: 날짜와 관련된 정보(분기의 첫날, 월의 마지막 날 등)을 미리 계산해 저장하므로, 쿼리 실행 시 복잡한 변환 작업 없이 바로 사용할 수 있다.

-- 날짜 차원(date dimension) 테이블 생성
drop table if exists public.date_dim;
create table public.date_dim
as
select date::date
, to_char(date,'yyyymmdd')::int as date_key
, date_part('day',date)::int as day_of_month
, date_part('doy',date)::int as day_of_year
, date_part('dow',date)::int as day_of_week
, trim(to_char(date, 'day')) as day_name
, trim(to_char(date, 'dy')) as day_short_name
, date_part('week',date)::int as week_number
, to_char(date,'w')::int as week_of_month
, date_trunc('week',date)::date as week
, date_part('month',date)::int as month_number
, trim(to_char(date, 'month')) as month_name
, trim(to_char(date, 'mon')) as month_short_name
, date_trunc('month',date)::date as first_day_of_month
, (date_trunc('month',date) + interval '1 month' - interval '1 day')::date as last_day_of_month
, date_part('quarter',date)::int as quarter_number
, trim('q' || date_part('quarter',date)::int) as quarter_name
, date_trunc('quarter',date)::date as first_day_of_quarter
, (date_trunc('quarter',date) + interval '3 months' - interval '1 day')::date as last_day_of_quarter
, date_part('year',date)::int as year 
, date_part('decade',date)::int * 10 as decade
, date_part('century',date)::int as century
from generate_series('1770-01-01'::date, '2020-12-31'::date, '1 day') as date

의원별 연차(period)를 계산하는 SQL문은 다음과 같다.

-- 시계열 데이터 결측 보간을 통한 리텐션 정확도 향상
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 1
		) 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 -- c.date가 해당 의원의 임기 기간에 속하는지
	 and c.month_name = 'December' and c.day_of_month = 31

✅ 결과 해석
B000944 의원을 보자.

• first_term은 1993-01-05으로 이는 첫 임기 시작일이다.
• term_start ~ term_end = 1993-01-05 ~ 1995-01-03으로 해당 기간 의원으로 근무했다.
• date가 1993-12-31일 때, 해당 의원은 근무중이었지만 1993-12-31 기준으로 대략 11개월차이므로 period는 0이다.
• date가 1994-12-31일 때, 해당 의원은 근무중이었고 1994-12-31 기준으로 대략 1년 11개월차이므로 period는 1이다.

⭐ term_start와 term_end는 고정되지만, period가 달라지면 행이 달라진다.
이렇게 만들어진 데이터는 의원 활동을 연도별로 세분화하고 시계열 분석에 활용할 수 있다.

1) 서브쿼리 1: 의원별 첫 임기 시작일 추출

select id_bioguide, min(term_start) as first_term
from legislators_terms
group by 1

• legislators_terms 테이블에서 각 의원(id_bioguide) 최초 임기 시작일(first_term)을 계산한다.
  
  

2) join문

join legislators_terms b on a.id_bioguide = b.id_bioguide

의원별 첫 임기 정보(a)와 모든 임기 정보(b)를 연결한다.

3) left join문: 날짜 차원 테이블과 연결

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

• c.date between b.term_start and b.term_end
  날짜(c.date)가 의원의 특정 임기(b.term_start~b.term_end)에 속하는지 확인한다.
• c.month_name = 'December' and c.day_of_month = 31
  날짜가 12월 31일인지 확인
• left join을 통해 b 테이블(의원의 임기 정보가 담긴 테이블)을 기준으로 하여 매칭되는 날짜가 없는 경우에도 b 테이블 데이터를 유지한다.
  
  

4) select문

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

• a.id_bioguide: 의원의 고유 ID
• a.first_term: 해당 의원의 첫 임기 시작일
• b.term_start, b.term_end: 해당 의원의 특정 임기의 시작일과 종료일
• c.date: 날짜 차원 테이블(date_dim)에서 선택된 연도별 12월 31일
• age(c.date, a.first_term): c.date와 a.first_term 간의 기간을 계산
• date_part('year', ...): 계산된 기간에서 연도만 추출
  
  

---

1-3. date 필드를 구간으로 한 코호트 크기 계산

1-2 에서 12월 31일을 기준으로 하는 의원별 재임 연도(연차)를 date 필드에 출력했다.
date 필드의 연차를 구간으로 삼아 id_bioguide의 개수를 세어 리텐션을 계산한다.

-- 재임 연도(연차)를 의미하는 date를 기준 구간으로 한 코호트 크기 계산
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 1
	) a
	join legislators_terms b on a.id_bioguide = b.id_bioguide
	left join date_dim c on date between b.term_start and b.term_end
	and c.month_name = 'December' and c.day_of_month = 31
group by 1

• coalesce(date_part('year', age(c.date, a.first_term)), 0) as period
  period 값이 null인 경우 0으로 대체한다.
  
  

---

1-4. 코호트 크기와 리텐션 비율 계산

select
	period
	,first_value(cohort_retained) over (order by period) as cochort_size
	,cohort_retained
	,cohort_retained * 1.0 / first_value(cohort_retained) over (order by period) 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 1
	) a
	join legislators_terms b on a.id_bioguide = b.id_bioguide
	left join date_dim c on date between b.term_start and b.term_end
	and c.month_name = 'December' and c.day_of_month = 31
	group by 1
) aa

• first_value 윈도우 함수를 사용해 코호트 크기(cohort_size)와 리텐션 비율(pct_retained)를 계산한다.
  
  

※ 코호트 크기, 리텐션 비율, 윈도우 함수 등에 대한 자세한 내용은 앞선 글을 참고해주세요.

📌 시행착오 기록

select
	coalesce(date_part('year', age(c.date, a.first_term)), 0) as period
	,count(distinct a.id_bioguide) as cohort_retained
	,count(distinct a.id_bioguide) * 1.0 / first_value(count(distinct a.id_bioguide)) over (order by coalesce(date_part('year', age(c.date, a.first_term)), 0)) as pct_retained
from
	(
		select id_bioguide, min(term_start) as first_term
		from legislators_terms
		group by 1
	) a
	join legislators_terms b on a.id_bioguide = b.id_bioguide
	left join date_dim c on date between b.term_start and b.term_end
	and c.month_name = 'December' and c.day_of_month = 31
group by 1

select절에서 period, cohort_retained를 정의해도 같은 select절 내에서 별칭으로 사용할 수 없기 때문에 쿼리의 가독성이 떨어진다.
이를 해결하기 위해서는 period, cohort_retained를 정의해주는 서브쿼리를 from절에 작성해야 한다.

---

1-5. 결과 해석 및 비교

• after) 임기 종료 날짜(term_end)를 활용한 리텐션 분석 결과
  

✅ 결과 해석
period가 1에서 2로 될 때, 리텐션 비율이 급락한다.
미국 국회의원의 임기가 2년 또는 6년이므로 대부분 선출되고 다음 해인 1번 구간까지 재임한다.
이후 재선에 실패하거나 도전하지 않는 국회의원이 많기 때문에 period가 급락한다.

• before) 임기 종료 날짜(term_end)를 활용하지 않은 리텐션 분석 결과
  

➡️ 리텐션 분석 결과는 전보다 더 정확해졌다.

---

2. 임기 종료 날짜가 결측값일 때, 리텐션 정확도 향상 방법

2-1. 시작 날짜에 고정된 인터벌 추가해 종료 날짜로 사용

의원의 임기, 서비스 구독 기간처럼 이벤트의 유지 기간을 이미 알고 있는 경우, 시작 날짜에 고정된 인터벌을 추가한다.

종료 날짜 = 시작 날짜 + 고정된 인터벌

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 1
) a
join legislators_terms b on a.id_bioguide = b.id_bioguide

1) case when문

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

임기 기간은 하원 의원(rep) 2년, 상원 의원(sen) 6년이다.

interval의 용법

• interval은 날짜차, 시간차를 나타내는 SQL 데이터 타입이다.
• interval은 숫자와 기간(예: years, months, days, hours)을 조합하여 기간을 표현할 수 있다.
• 날짜와 interval 타입 간의 계산은 SQL이 내부적으로 처리하므로 사용자는 간단히 +, - 연산자로 기간 계산을 수행할 수 있다.

2 years

• '2 years'는 interval 타입으로 변환 가능한 리터럴 값(literal)이다.
• SQL 동작으로 '2 years'처럼 작성된 문자열 리터럴이 interval 타입으로 자동 변환된다.

2) 결론

시작 날짜에 고정된 인터벌을 추가해 종료 날짜로 활용하는 해당 방법은 "의원이 정해진 임기를 다 채운다고 가정한다."
따라서, 임기를 다 채우지 못하는 경우를 고려하지 못한다는 단점이 있다.

---

2-2. 다음 임기의 시작 날짜에서 하루를 뺀 값을 종료 날짜로 사용

select 
	a.id_bioguide, a.first_term
	,b.term_start
	,lead(b.term_start) over (partition by a.id_bioguide order by b.term_start) - interval '1 day' as term_end
from
(
	select id_bioguide, min(term_start) as first_term
	from legislators_terms
	group by 1
) a
join legislators_terms b on a.id_bioguide = b.id_bioguide
order by 1, 3

1) lead 함수

lead(b.term_start) over (partition by a.id_bioguide order by b.term_start) - interval '1 day' as term_end

• partition by a.id_bioguide: 의원별로 데이터를 그룹화한다.
• order by b.term_start: 의원이 임기 시작일을 기준으로 정렬한다.
• lead(b.term_start): 현재 행 기준으로 다음 행의 b.term_start를 가져온다.
• 다음 행의 b.term_start에서 하루를 뺀 것을 임기 종료일(term_end)로 사용한다.
  
  

2) 결론

lead 함수를 사용할 경우, 의원직을 한 번만 수행해서 다음 임기가 없는 경우 null을 반환하므로 term_end에 null이 저장된다는 단점이 있다.
의원 임기가 항상 연속적으로 이어진다고 가정한다는 단점이 있다.

이어서, 코호트 그룹을 만드는 방법과 그룹별 리텐션 분석 방법을 알아보자.

---

💡 질문과 피드백 사항은 댓글에 편하게 남겨주시기 바랍니다.
❤️ 도움이 되셨다면 공감 부탁드립니다.