Census reporting views

census_reporting_views.sql

-- View for the daily tract numbers
drop view if exists daily_tract_rates;
create view daily_tract_rates as (
	select
		geo_id,
		right(geo_id, 6) as tract,
		resp_date::date,
		drrint::numeric,
		drrall::numeric,
		crrint::numeric,
		crrall::numeric
	from
	tract_response_rates
);

-- View for the daily city numbers
drop view if exists daily_city_rates;
create view daily_city_rates as (
	select 
		c.city as city,
		crr.resp_date::date as resp_date,
		crr.crrall::numeric as crrall
	from city_response_rates crr
		inner join cities c on c.geoid = crr.geo_id
);

-- View for the daily neighborhood numbers
drop view if exists daily_neighborhood_rates;
create or replace view daily_neighborhood_rates as (
with tract_nhood_by_day as (
	with latest_tract_rates as
	(
		select 
			right(geo_id, 6) as tract,
			crrall,
			resp_date
		from tract_response_rates
	)
	select 
		tractce as tract, 
		"Neighborhood" as nhood, 
		replace("HH", ',', '')::numeric as hh_ct,
		((crrall / 100) * replace("HH", ',', '')::numeric) as response_portion,
		ltr.resp_date,
		ltr.crrall
	from neighborhood_tract_xwalk ntx
		inner join
		latest_tract_rates ltr on ntx.tractce::text = ltr.tract::text
)
select 
	resp_date::date,
	nhood,
	round(sum(response_portion)::numeric) as tract_absolute_responses,
	sum(hh_ct) as total_tract_hh,
	round((sum(response_portion) / sum(hh_ct) * 100)::numeric, 1) as crrall_on_resp_date
from tract_nhood_by_day
group by resp_date, nhood
);

-- View for the daily district numbers
drop view if exists daily_district_rates;
create view daily_district_rates as (
	WITH tract_district_by_day AS (
		WITH latest_tract_rates AS ( SELECT RIGHT ( geo_id, 6 ) AS tract, crrall, resp_date FROM tract_response_rates ) SELECT
		tractce AS tract,
		"District" AS district,
		REPLACE ( "HH", ',', '' ) :: NUMERIC AS hh_ct,
		( ( crrall / 100 ) * REPLACE ( "HH", ',', '' ) :: NUMERIC ) AS response_portion,
		ltr.resp_date,
		ltr.crrall 
		FROM
			neighborhood_tract_xwalk ntx
			INNER JOIN latest_tract_rates ltr ON ntx.tractce :: TEXT = ltr.tract :: TEXT 
		) SELECT
		resp_date :: DATE,
		district,
		round( SUM ( response_portion ) :: NUMERIC ) AS tract_absolute_responses,
		SUM ( hh_ct ) AS total_tract_hh,
		round( ( SUM ( response_portion ) / SUM ( hh_ct ) * 100 ) :: NUMERIC, 1 ) AS crrall_on_resp_date 
	FROM
		tract_district_by_day 
	GROUP BY
		resp_date,
		district 
	ORDER BY
		resp_date,
		district 
	)