--deal with missing leading 0s if a problem
update realtor_data set postal_code = '0'||postal_code where length(postal_code) = 4;


--set back to null for everything to clear old data
update zip_codes  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 zip_codes 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.geoid10, b.median_listing_price::double precision, b.median_listing_price_mm::double precision, b.median_listing_price_yy::double precision,
	b.active_listing_count::double precision, b.active_listing_count_mm::double precision, b.active_listing_count_yy::double precision,
	b.median_days_on_market::double precision, b.median_days_on_market_mm::double precision, b.median_days_on_market_yy::double precision,
	b.new_listing_count::double precision, b.new_listing_count_mm::double precision, b.new_listing_count_yy::double precision,
	b.price_increased_count::double precision, b.price_increased_count_mm::double precision, b.price_increased_count_yy::double precision,
	b.price_reduced_count::double precision, b.price_reduced_count_mm::double precision, b.price_reduced_count_yy::double precision,
	b.pending_listing_count::double precision, b.pending_listing_count_mm::double precision, b.pending_listing_count_yy::double precision,
	b.median_listing_price_per_square_foot::double precision, b.median_listing_price_per_square_foot_mm::double precision, b.median_listing_price_per_square_foot_yy::double precision,
	b.median_square_feet::double precision, b.median_square_feet_mm::double precision, b.median_square_feet_yy::double precision,
	b.average_listing_price::double precision, b.average_listing_price_mm::double precision, b.average_listing_price_yy::double precision,
	b.total_listing_count::double precision, b.total_listing_count_mm::double precision, b.total_listing_count_yy::double precision,
	b.pending_ratio::double precision --b.pending_ratio_mm::double precision, 
  --b.pending_ratio_yy::double precision
	from zip_codes a
	join realtor_data b
	on a.geoid10 = b.postal_code
)sq
where sq.geoid10 = zip_codes.geoid10;

/*
--interpolating the data to fill in missing zips
update zip_codes set median_listing_price = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_listing_price) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_listing_price is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_listing_price_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_listing_price_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_listing_price_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_listing_price_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_listing_price_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_listing_price_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set active_listing_count = sq.avg
from (
	select b.gid, b.geoid10, avg(a.active_listing_count) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.active_listing_count is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set active_listing_count_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.active_listing_count_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.active_listing_count_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set active_listing_count_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.active_listing_count_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.active_listing_count_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_days_on_market = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_days_on_market) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_days_on_market is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_days_on_market_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_days_on_market_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_days_on_market_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_days_on_market_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_days_on_market_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_days_on_market_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set new_listing_count = sq.avg
from (
	select b.gid, b.geoid10, avg(a.new_listing_count) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.new_listing_count is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set new_listing_count_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.new_listing_count_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.new_listing_count_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set new_listing_count_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.new_listing_count_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.new_listing_count_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set price_increased_count = sq.avg
from (
	select b.gid, b.geoid10, avg(a.price_increased_count) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.price_increased_count is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set price_increased_count_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.price_increased_count_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.price_increased_count_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set price_increased_count_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.price_increased_count_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.price_increased_count_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set price_reduced_count = sq.avg
from (
	select b.gid, b.geoid10, avg(a.price_reduced_count) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.price_reduced_count is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set price_reduced_count_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.price_reduced_count_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.price_reduced_count_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set price_reduced_count_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.price_reduced_count_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.price_reduced_count_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set pending_listing_count = sq.avg
from (
	select b.gid, b.geoid10, avg(a.pending_listing_count) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.pending_listing_count is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set pending_listing_count_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.pending_listing_count_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.pending_listing_count_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set pending_listing_count_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.pending_listing_count_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.pending_listing_count_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_listing_price_per_square_feet = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_listing_price_per_square_feet) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_listing_price_per_square_feet is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_listing_price_per_square_feet_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_listing_price_per_square_feet_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_listing_price_per_square_feet_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_listing_price_per_square_feet_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_listing_price_per_square_feet_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_listing_price_per_square_feet_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_square_feet = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_square_feet) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_square_feet is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_square_feet_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_square_feet_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_square_feet_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set median_square_feet_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.median_square_feet_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.median_square_feet_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set average_listing_price = sq.avg
from (
	select b.gid, b.geoid10, avg(a.average_listing_price) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.average_listing_price is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set average_listing_price_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.average_listing_price_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.average_listing_price_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set average_listing_price_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.average_listing_price_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.average_listing_price_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set total_listing_count = sq.avg
from (
	select b.gid, b.geoid10, avg(a.total_listing_count) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.total_listing_count is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set total_listing_count_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.total_listing_count_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.total_listing_count_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set total_listing_count_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.total_listing_count_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.total_listing_count_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set pending_ratio = sq.avg
from (
	select b.gid, b.geoid10, avg(a.pending_ratio) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.pending_ratio is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set pending_ratio_mm = sq.avg
from (
	select b.gid, b.geoid10, avg(a.pending_ratio_mm) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.pending_ratio_mm is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;

update zip_codes set pending_ratio_yy = sq.avg
from (
	select b.gid, b.geoid10, avg(a.pending_ratio_yy) as avg
	from zip_codes a
	join zip_codes b on st_intersects(a.geom,b.geom)
	where a.gid != b.gid
	and b.pending_ratio_yy is null
	group by b.gid, b.geoid10
) sq
where sq.gid = zip_codes.gid;
*/
