`queries/current_permit_leads.sql`

28 lines
-- Current building-permit LEADS: recently-issued residential permits, enriched
-- with the parcel's assessed value / year built / size (permit.apn = parcel.ain).
-- "Best leads" = active (Issued), residential, real valuation, freshly issued.
CREATE OR REPLACE VIEW current_permit_leads AS
SELECT
  p.issue_date::date            AS issued,
  p.primary_address             AS address,
  p.zip_code                    AS zip,
  p.council_district            AS cd,
  p.permit_type                 AS work_type,
  p.valuation                   AS project_value,
  p.work_desc                   AS scope,
  p.apn,
  a.total_value                 AS assessed_value,
  a.year_built,
  a.sqft_main                   AS sqft,
  a.use_type,
  p.lat, p.lon
FROM la_building_permits_raw p
LEFT JOIN la_assessor_parcels_raw a
  ON a.ain = p.apn AND a.roll_year = '2025'
WHERE p.dataset_id = 'pi9x-tg5x'
  AND p.status_desc = 'Issued'
  AND p.permit_sub_type = '1 or 2 Family Dwelling'
  AND p.issue_date >= now() - interval '60 days'
  AND p.valuation >= 25000
  AND p.primary_address IS NOT NULL;