`scripts/classify-broker-agent-type.sql`

39 lines
-- classify-broker-agent-type.sql — name-based commercial/residential classifier for the CRCP `cre` DB.
-- Mirrors usre's classify-asset-class.sql rules BUT with a CONSERVATIVE default (else = KEEP current),
-- because CRCP's broker table is a CURATED CRE set (pre-filtered to brokers with commercial listings),
-- so the usre "else -> residential" default would wrongly gut the desk. Approved by Steve 2026-08-14
-- (TK-10583, "Conservative"): apply only the DEFINITE name signals, leave ambiguous curated brokers as-is.
--
-- Rule (top-down, first match wins), evaluated on the broker's FIRM name:
--   1. BRAND match             -> commercial   (named national CRE house)
--   2. "residential" in name   -> residential   (e.g. Coldwell Banker Residential, Del Rey Residential)
--   3. NON-BROKER match        -> residential   (mortgage/lender/insurer/escrow/title/bank/appraiser/prop-mgmt)
--   4. KEYWORD match           -> commercial   (commercial real estate / net lease / CRE / ...)
--   5. else                    -> KEEP current  (curated CRE membership itself is the commercial signal;
--                                                firm-less brokers also keep current)
-- Re-runnable / idempotent. The nightly Redfin/SFR importers re-assert their own residential tags,
-- which this classifier's rule 2/3/else all leave intact.

\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'
\set kw_rx 'commercial real estate|\\ycommercial\\y|net lease|capital markets|investment sales|industrial realty|\\ycre\\y'
\set nonbroker_rx 'mortgage|lending|\\yloans?\\y|insurance|escrow|\\ytitle\\y|\\ybank\\y|apprais|property management'

BEGIN;

UPDATE broker b
   SET agent_type = CASE
         WHEN f.name IS NULL                       THEN b.agent_type
         WHEN lower(f.name) ~ :'brand_rx'          THEN 'commercial'
         WHEN lower(f.name) ~ '\yresidential\y'    THEN 'residential'
         WHEN lower(f.name) ~ :'nonbroker_rx'      THEN 'residential'
         WHEN lower(f.name) ~ :'kw_rx'             THEN 'commercial'
         ELSE b.agent_type
       END
  FROM firm f
 WHERE b.firm_id = f.id;

COMMIT;

-- report
SELECT agent_type, count(*) FROM broker GROUP BY agent_type ORDER BY 2 DESC;