Number of females from each state in congress by year

congressGender.sql

--% of females elected to congress each y ear

with fLegs AS(
	select date_part('year', start_date) as dated, state, count(b) as cnt
	from persons a, person_roles b 
	where a.id = b.person_id
	AND a.gender = 'F'
	GROUP BY state, dated
	ORDER BY dated
	),

allZeroes AS(
	select date_part('year', start_date) as dated, state, 0 as cnt
	from persons a, person_roles b 
	where a.id = b.person_id
	GROUP BY state, dated
	ORDER BY dated
	),

midMerge AS(
	select * from allZeroes
	WHERE NOT EXISTS(
		select * from fLegs
		WHERE allZeroes.dated = fLegs.dated
		AND allZeroes.state = fLegs.state )
	),

allLegs AS(
	select date_part('year', start_date) as dated, state, count(b) as cnt
	from persons a, person_roles b 
	where a.id = b.person_id
	AND date_part('year', start_date) > 1860
	GROUP BY state, dated
	ORDER BY dated, state
),

fLegsFinal AS(
	select * from midMerge
	WHERE dated > 1860
	UNION
	select * from fLegs
	WHERE dated > 1860
	ORDER BY dated, state
)

select a.state, a.dated, a.cnt as females, b.cnt as total, ((b.cnt)::float/(a.cnt)::float)
from allLegs a, fLegsFinal b
WHERE a.state = b.state
AND a.dated = b.dated;