Properties

DROP VIEW IF EXISTS VW_trans_composite_eth_prop;

CREATE VIEW VW_trans_composite_eth_prop AS

(
SELECT 

seller_titles.ttl_title_id AS seller_title_id, seller_titles.ttl_date AS seller_title_date, seller_titles.ttl_title_code AS seller_title_code,
buyer_titles.ttl_title_id AS buyer_title_id, buyer_titles.ttl_date AS buyer_title_date, buyer_titles.ttl_title_code AS buyer_title_code,
DATEDIFF(buyer_titles.ttl_date, seller_titles.ttl_date) as seller_duration_days,
census_tracts.census_tract_code,
props.*,
sellers.concat_owners AS concat_sellers, sellers.concat_ethnicities AS seller_ethnicities, sellers.total_owners AS total_sellers,
sellers.munged_ethnicity AS seller_munged_eth,
sellers.total_institutional AS institutional_sellers,
buyers.concat_owners AS concat_buyers, buyers.concat_ethnicities AS buyer_ethnicities, buyers.total_owners AS total_buyers,
buyers.munged_ethnicity AS buyer_munged_eth,
buyers.total_institutional AS institutional_buyers

FROM titles AS buyer_titles
LEFT JOIN titles_to_prectitles ON buyer_titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
LEFT JOIN titles AS seller_titles ON seller_titles.ttl_title_id = titles_to_prectitles.ttp_prectitle_id_fk
LEFT JOIN VW_titles_composite_eth AS sellers ON seller_titles.ttl_title_id = sellers.ttl_title_id
LEFT JOIN VW_titles_composite_eth AS buyers ON buyer_titles.ttl_title_id = buyers.ttl_title_id

LEFT JOIN props AS props ON seller_titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id

ORDER BY seller_titles.ttl_title_id
)