update counties set price_income_ratio = sq.price_income_ratio, population_density = sq.population_density, inventory_ratio = sq.inventory_ratio
from (
 select a.geoid, a.median_listing_price, b.median_hh_income, b.population, b.total_housing_units, 
  (a.median_listing_price::int/b.median_hh_income::int) as price_income_ratio,
  ROUND((b.population::int/(a.aland * 3.86102e-7)))  as population_density, 
  (b.total_housing_units::int/a.active_listing_count::int) as inventory_ratio
  from counties a
  join county_demographics b
  on a.geoid = b.fips
  where a.median_listing_price is not null
  AND b.median_hh_income is not null
  AND active_listing_count::int > 0) sq
where sq.geoid = counties.geoid;

-- monthly cost variable: median listing price, 0.2 assumed down payment, 30 year fixed. 1.075 is the interest rate, 5000 is taxes + insurance, 12 months

update counties set monthly_cost = ((median_listing_price::int - (median_listing_price::int * 0.2)) / 30 * 1.075 +  monthly_insurance::int + (median_listing_price::int * (state_income_tax_rate::double precision/100)/ 12))/ 12;
