← back to Nationalrealestate

db/views/last_broker_of_record.sql

18 lines

-- TK-10139: newest broker/firm of record per address_key (the "Last Broker of Record"
-- + "Last Firm of Record" surface). DISTINCT ON picks the most-recent observation per key.
CREATE OR REPLACE VIEW last_broker_of_record AS
SELECT DISTINCT ON (address_key)
  address_key,
  parcel_key,
  county_fips,
  ain,
  address,
  city,
  broker_id,
  broker_name,
  firm_id,
  firm_name,
  observed_at
FROM broker_of_record_history
ORDER BY address_key, observed_at DESC;