--fix the missing '0' issue for fips codes
update realtor_data_county set county_fips = '0'||county_fips where length(county_fips) = 4;

--set back to null for everything to clear old data
update counties  set median_listing_price = null, median_listing_price_mm = null, median_listing_price_yy = null,
active_listing_count = null, active_listing_count_mm = null, active_listing_count_yy = null,
median_days_on_market = null, median_days_on_market_mm = null, median_days_on_market_yy = null,
new_listing_count = null, new_listing_count_mm = null, new_listing_count_yy = null,
price_increased_count = null, price_increased_count_mm = null, price_increased_count_yy = null,
price_reduced_count = null, price_reduced_count_mm = null, price_reduced_count_yy = null,
pending_listing_count = null, pending_listing_count_mm = null, pending_listing_count_yy = null,
median_listing_price_per_square_feet = null, median_listing_price_per_square_feet_mm = null, median_listing_price_per_square_feet_yy = null,
median_square_feet = null, median_square_feet_mm = null, median_square_feet_yy = null,
average_listing_price = null, average_listing_price_mm = null, average_listing_price_yy = null,
total_listing_count = null, total_listing_count_mm = null, total_listing_count_yy = null,
pending_ratio = null, pending_ratio_mm = null, pending_ratio_yy = null;

--theres a data bug in pending ration mm #NAME? or Infinity can't be converted to double precision
update counties set median_listing_price = sq.median_listing_price, median_listing_price_mm = sq.median_listing_price_mm, median_listing_price_yy = sq.median_listing_price_yy,
active_listing_count = sq.active_listing_count, active_listing_count_mm = sq.active_listing_count_mm, active_listing_count_yy = sq.active_listing_count_yy,
median_days_on_market = sq.median_days_on_market, median_days_on_market_mm = sq.median_days_on_market_mm, median_days_on_market_yy = sq.median_days_on_market_yy,
new_listing_count = sq.new_listing_count, new_listing_count_mm = sq.new_listing_count_mm, new_listing_count_yy = sq.new_listing_count_yy,
price_increased_count = sq.price_increased_count, price_increased_count_mm = sq.price_increased_count_mm, price_increased_count_yy = sq.price_increased_count_yy,
price_reduced_count = sq.price_reduced_count, price_reduced_count_mm = sq.price_reduced_count_mm, price_reduced_count_yy = sq.price_reduced_count_yy,
pending_listing_count = sq.pending_listing_count, pending_listing_count_mm = sq.pending_listing_count, pending_listing_count_yy = sq.pending_listing_count_yy,
median_listing_price_per_square_feet = sq.median_listing_price_per_square_foot, median_listing_price_per_square_feet_mm = sq.median_listing_price_per_square_foot_mm, median_listing_price_per_square_feet_yy = sq.median_listing_price_per_square_foot_yy,
median_square_feet = sq.median_square_feet, median_square_feet_mm = sq.median_square_feet_mm, median_square_feet_yy = sq.median_square_feet_yy,
average_listing_price = sq.average_listing_price, average_listing_price_mm = sq.average_listing_price_mm, average_listing_price_yy = sq.average_listing_price_yy,
total_listing_count = sq.total_listing_count, total_listing_count_mm = sq.total_listing_count_mm, total_listing_count_yy = sq.total_listing_count_yy,
pending_ratio = sq.pending_ratio --pending_ratio_mm = sq.pending_ratio_mm,
--pending_ratio_yy = sq.pending_ratio_yy
from (
  select a.geoid, b.median_listing_price, b.median_listing_price_mm, b.median_listing_price_yy,
  b.active_listing_count, b.active_listing_count_mm, b.active_listing_count_yy,
  b.median_days_on_market, b.median_days_on_market_mm, b.median_days_on_market_yy,
  b.new_listing_count, b.new_listing_count_mm, b.new_listing_count_yy,
  b.price_increased_count, b.price_increased_count_mm, b.price_increased_count_yy,
  b.price_reduced_count, b.price_reduced_count_mm, b.price_reduced_count_yy,
  b.pending_listing_count, b.pending_listing_count_mm, b.pending_listing_count_yy,
  b.median_listing_price_per_square_foot, b.median_listing_price_per_square_foot_mm, b.median_listing_price_per_square_foot_yy,
  b.median_square_feet, b.median_square_feet_mm, b.median_square_feet_yy,
  b.average_listing_price, b.average_listing_price_mm, b.average_listing_price_yy,
  b.total_listing_count, b.total_listing_count_mm, b.total_listing_count_yy,
  b.pending_ratio --b.pending_ratio_mm,
  --b.pending_ratio_yy
  from counties a
  join realtor_data_county b
  on a.geoid = b.county_fips
)sq
where sq.geoid = counties.geoid;

