SQL로 시작하는 데이터 분석

202

SQL로 시작하는 데이터 분석

FROM

legislators_terms

) d

JOIN

legislators_terms e

on

d.id_bioguide = e.id_bioguide

LEFT

JOIN

date_dim f

on

f.date

between

e.term_start

and

e.term_end

and

f.month_name = ‘December’

and

f.day_of_month = 31

JOIN

legislators g

on

d.id_bioguide = g.id_bioguide

WHERE

d.first_term

between

‘1917-01-01’

and

‘1999-12-31’

GROUP

BY

1,2,3

) ddd

on

aaa.gender = ddd.gender

and

aaa.first_state = ddd.first_state

and

aaa.period = ddd.period

ORDER

BY

1,2,3

;

gender first_state period cohort_size cohort_retained pct_retained

------ ----------- ------ ----------- --------------- ------------

F AL 0 3 3 1.0000

F AL 1 3 1 0.3333

F AL 2 ...