← back to Commercialrealestate

scripts/db/price-drop-migration.sql

16 lines

-- Price-change tracking: prev_price (last different price) + price_changed_at (when it last changed).
DO $$ DECLARE t text; BEGIN
  FOREACH t IN ARRAY ARRAY['sfr','condo'] LOOP
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS prev_price numeric', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS price_changed_at timestamptz', t);
  END LOOP;
END $$;
CREATE OR REPLACE VIEW condo_card AS
 SELECT c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
        c.project_name, c.warrantable_status, c.warrant_source, c.warrant_signals, c.broker_name,
        COALESCE(c.firm_name, f.name) AS firm_name, c.source, c.created_at,
        c.status, c.last_seen, c.off_market_at, c.disposition, c.sold_price, c.sold_date,
        c.days_on_market, c.listed_date, c.market_status, c.prev_market_status,
        c.prev_price, c.price_changed_at, c.lat, c.lng
   FROM condo c LEFT JOIN firm f ON f.id = c.firm_id;