← back to Nationalrealestate

db/views/recent_commercial_deals.sql

17 lines

CREATE OR REPLACE VIEW recent_commercial_deals AS
 SELECT pe.event_date AS sale_date,
    pe.amount AS sale_price,
    cp.ctype,
    cp.address,
    cp.city,
    cp.county_fips,
    r.name AS county_name,
    cp.sqft,
    cp.year_built,
    pe.doc_number
   FROM parcel_event pe
     JOIN commercial_parcel cp ON cp.county_fips = pe.county_fips AND cp.ain = pe.source_id
     LEFT JOIN region r ON r.fips = pe.county_fips AND r.region_type = 'county'::text
  WHERE pe.event_type = 'sale'::text AND pe.amount > 250000::numeric AND pe.event_date >= (CURRENT_DATE - '1 year 6 mons'::interval)
  ORDER BY pe.event_date DESC, pe.amount DESC;