Finding assessed values of two-flats in Chicago with and without coach houses

two_flats_with_coach_houses.sql

/* select properties with coach houses */
with properties as (select 
	pt.pin14,
	pt.total_assessed_value,
	pin_num_cards > 1 as has_coach_house
from assessor_single_mf_characteristics AS ac inner join propertytaxes_combined AS pt on ac.pin = pt.pin14
where year = '2023'
	and city = 'CHICAGO'
	and property_Class = '2-11'
	and ac.units = 2
	and township_code = '77'
	and char_land_sf between 3000 and 3300
), stats as (select
	avg(total_assessed_value) as mean,
	min(total_assessed_value), 
	max(total_assessed_value),
	stddev(total_assessed_value) as stddev,
	count(*)
from properties
group by has_coach_house
) select
	count(*),
	avg(total_assessed_value) as mean_assessed_value,
	has_coach_house,
	total_assessed_value between mean-stddev and mean+stddev as within_one_sd
from properties left join stats on total_assessed_value between mean-stddev and mean+stddev
group by has_coach_house, within_one_sd;