`scripts/classify-asset-class.sql`

61 lines
-- classify-asset-class.sql — idempotent commercial/residential classifier for usre.
-- Re-runnable: safe to run any number of times. NEVER overwrites a row whose
-- asset_class_source = 'manual' (human override wins).
--
-- Rule (evaluated top-down, first match wins):
--   1. BRAND match  -> commercial   (a named national CRE house; wins even if the name also
--                                     contains a lender word, e.g. "Berkadia Commercial Mortgage")
--   2. "residential" in name -> residential   (explicit; keeps Coldwell Banker RESIDENTIAL out)
--   3. NON-BROKER match -> residential   (mortgage / lender / insurer / escrow / title / bank /
--                                          appraiser / property-mgmt: NOT a CRE brokerage, so it must
--                                          not appear on RENTV's CRE broker desk. Only reached when
--                                          no CRE brand matched, so it can't demote a real CRE house.)
--   4. KEYWORD match -> commercial   (commercial real estate / net lease / capital markets /
--                                      investment sales / industrial realty / bare "commercial" / cre)
--   5. else -> residential   (the ~215k-firm registry is overwhelmingly residential)
-- Brokers inherit their firm's class; firm-less brokers default residential.

-- 1) Named national CRE houses. Short/ambiguous tokens use word boundaries (\y).
\set brand_rx '\\y(cbre|jll|nai|cbc|svn|ipa)\\y|jones lang lasalle|cushman|colliers|newmark|marcus (&|and) millichap|kidder mathews|lee (&|and) associates|avison young|savills|cresa|institutional property advisors|stream realty|matthews (real estate|reis)|northmarq|berkadia|walker (&|and) dunlop|eastdil|transwestern|srs real estate|hanley investment|coldwell banker commercial|keller williams commercial|sperry van ness|tcn worldwide|voit real estate|daum commercial'
-- 4) Generic commercial signals (weaker than a brand; gated by the non-broker guard above).
\set kw_rx 'commercial real estate|\\ycommercial\\y|net lease|capital markets|investment sales|industrial realty|\\ycre\\y'
-- 3) Not a CRE brokerage — lenders, insurers, title/escrow, banks, appraisers, property managers.
\set nonbroker_rx 'mortgage|lending|\\yloans?\\y|insurance|escrow|\\ytitle\\y|\\ybank\\y|apprais|property management'

BEGIN;

UPDATE firm
   SET asset_class = CASE
         WHEN lower(coalesce(normalized_name, name)) ~ :'brand_rx'     THEN 'commercial'
         WHEN lower(coalesce(normalized_name, name)) ~ '\yresidential\y' THEN 'residential'
         WHEN lower(coalesce(normalized_name, name)) ~ :'nonbroker_rx' THEN 'residential'
         WHEN lower(coalesce(normalized_name, name)) ~ :'kw_rx'        THEN 'commercial'
         ELSE 'residential'
       END,
       asset_class_source = 'seed'
 WHERE asset_class_source IS DISTINCT FROM 'manual';

-- Brokers with a firm inherit the firm's class.
UPDATE broker b
   SET asset_class = f.asset_class,
       asset_class_source = 'inherited'
  FROM firm f
 WHERE b.firm_id = f.id
   AND b.asset_class_source IS DISTINCT FROM 'manual';

-- Firm-less brokers (standalone DRE licensees): residential by default. RENTV drops these via
-- firm_id IS NOT NULL anyway, but tagging keeps usreal/CRCP correct.
UPDATE broker
   SET asset_class = 'residential',
       asset_class_source = 'seed'
 WHERE firm_id IS NULL
   AND asset_class_source IS DISTINCT FROM 'manual';

COMMIT;

-- report
SELECT 'firm' AS tbl, asset_class, count(*) FROM firm GROUP BY asset_class
UNION ALL
SELECT 'broker' AS tbl, asset_class, count(*) FROM broker GROUP BY asset_class
ORDER BY tbl, asset_class;