관련 코호트 분석 - 생존자

생존자

id별 첫 임기 시작날짜와 마지막 임기 시작 날짜

SELECT
	id_bioguide,
	MIN(term_start) AS first_term,
	MAX(term_start) AS last_term
FROM legislators_terms
GROUP BY 1

tenure 계산

SELECT
	id_bioguide,
	MIN(term_start) AS first_term,
	MAX(term_start) AS last_term,
	DATE_PART('year', AGE(MAX(term_start), MIN(term_start))) AS tenure
FROM legislators_terms
GROUP BY 1

century별 코호트 숫자와 임기가 10년 이상인 경우의 비율

SELECT
	first_century,
	COUNT(DISTINCT id_bioguide) AS cohort_size,
	COUNT(DISTINCT CASE WHEN tenure >= 10 THEN id_bioguide END) AS survived_10,
	ROUND(COUNT(DISTINCT CASE WHEN tenure >= 10 THEN id_bioguide END)*1.0/COUNT(DISTINCT id_bioguide), 2) AS pct_survived_10
FROM
(
SELECT
	id_bioguide,
	DATE_PART('century', MIN(term_start)) AS first_century,
	MIN(term_start) AS first_term,
	MAX(term_start) AS last_term,
	DATE_PART('year', AGE(MAX(term_start), MIN(term_start))) AS tenure
FROM legislators_terms
GROUP BY 1
) AS sub
GROUP BY 1

century별 코호트 숫자와 임기가 5년 이상인 경우의 비율

SELECT
	first_century,
	COUNT(DISTINCT id_bioguide) AS cohort_size,
	COUNT(DISTINCT CASE WHEN total_terms >= 5 THEN id_bioguide END) AS survived_5,
	ROUND(COUNT(DISTINCT CASE WHEN total_terms >= 5 THEN id_bioguide END)*1.0/COUNT(DISTINCT id_bioguide), 2) AS pct_survived_5
FROM
(
SELECT
	id_bioguide,
	DATE_PART('century', MIN(term_start)) AS first_century,
	COUNT(term_start) AS total_terms
FROM legislators_terms
GROUP BY 1
) AS sub
GROUP BY 1

century, terms별 코호트 숫자와 비율

• 1년부터 20년까지 terms을 생성하고 JOIN한다.
• 각 세기별 고유한 id의 개수를 센다.
• 각 terms에 해당하는 임기에 대해 고유한 id의 개수를 센다.
• 비율을 계산한다.

SELECT
	A.first_century,
	B.terms,
	COUNT(DISTINCT A.id_bioguide) AS cohort,
	COUNT(DISTINCT CASE WHEN A.total_terms >= B.terms THEN A.id_bioguide END) AS cohort_survived,
	ROUND(COUNT(DISTINCT CASE WHEN A.total_terms >= B.terms THEN A.id_bioguide END)*1.0/COUNT(DISTINCT id_bioguide), 2) AS pct_cohort_survived
FROM
(
SELECT
	id_bioguide,
	DATE_PART('century', MIN(term_start)) AS first_century,
	COUNT(term_start) AS total_terms
FROM legislators_terms
GROUP BY 1
) AS A
	JOIN (SELECT generate_series AS terms FROM generate_series(1, 20, 1)) AS B
		ON 1 = 1
GROUP BY 1, 2