Sample queries

Ready-to-run SQL against the PropRaven Marketplace catalog. Copy any block into Snowsight after subscribing to the relevant share.

-- ============================================================================
-- PropRaven Marketplace v1 — Sample queries
--
-- Every query here is a representative customer use-case. Copy/adapt freely.
-- All queries assume access to PROPRAVEN_MARKETPLACE.{SILVER,GOLD_GRAPH,GOLD_INTELLIGENCE}.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. PARCEL DISCOVERY: find every parcel in a target metro
-- ----------------------------------------------------------------------------
-- Charlotte MSA (CBSA 16740): all single-family parcels with lot >= 0.5 ac
SELECT
  p.prpv_parcel_id, p.apn, p.legal_description,
  pr.year_built, pr.bedroom_count, pr.bathroom_count,
  p.lot_size_acres
FROM PROPRAVEN_MARKETPLACE.SILVER.PARCEL p
JOIN PROPRAVEN_MARKETPLACE.SILVER.PROPERTY pr  ON pr.prpv_parcel_id = p.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.SILVER.GEOGRAPHY g  ON g.prpv_geography_id = p.prpv_geography_id
WHERE g.cbsa_code = '16740'
  AND pr.property_type_class = 'SFR'
  AND p.lot_size_acres >= 0.5
LIMIT 100;


-- ----------------------------------------------------------------------------
-- 2. INSTITUTIONAL OWNERSHIP: parcels owned by Invitation Homes nationally
-- ----------------------------------------------------------------------------
SELECT
  o.name_raw,
  pa.state_fips, pa.county_fips,
  COUNT(*) AS parcels
FROM PROPRAVEN_MARKETPLACE.SILVER.OWNER o
JOIN PROPRAVEN_MARKETPLACE.GOLD_GRAPH.EDGE_OWNER_PARCEL eop ON eop.prpv_owner_id = o.prpv_owner_id
JOIN PROPRAVEN_MARKETPLACE.SILVER.PARCEL pa                ON pa.prpv_parcel_id = eop.prpv_parcel_id
WHERE UPPER(o.name_normalized) LIKE '%INVITATION HOMES%'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;


-- ----------------------------------------------------------------------------
-- 3. ABSENTEE INVENTORY: out-of-state owners in your market
-- ----------------------------------------------------------------------------
-- Mecklenburg County NC, owners mailing from out of state, parcels with TAV >= $200k
SELECT
  pa.prpv_parcel_id, pa.apn, pa.legal_description,
  ab.mailing_state, ab.mailing_city,
  o.name_raw, o.entity_type,
  t.assessed_value_total
FROM PROPRAVEN_MARKETPLACE.SILVER.PARCEL pa
JOIN PROPRAVEN_MARKETPLACE.GOLD_INTELLIGENCE.PARCEL_ABSENTEE ab ON ab.prpv_parcel_id = pa.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.GOLD_GRAPH.EDGE_OWNER_PARCEL eop    ON eop.prpv_parcel_id = pa.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.SILVER.OWNER o                       ON o.prpv_owner_id = eop.prpv_owner_id
LEFT JOIN PROPRAVEN_MARKETPLACE.SILVER.TAX_RECORD t
  ON t.prpv_parcel_id = pa.prpv_parcel_id
 AND t.tax_year = 2024
WHERE pa.state_fips = '37' AND pa.county_fips = '119'
  AND ab.absentee_class = 'OUT_OF_STATE'
  AND ab.confidence_score >= 0.95
  AND t.assessed_value_total >= 200000
ORDER BY t.assessed_value_total DESC
LIMIT 500;


-- ----------------------------------------------------------------------------
-- 4. PORTFOLIO INTELLIGENCE: who owns more than 100 parcels in California?
-- ----------------------------------------------------------------------------
SELECT
  op.name_raw, op.entity_type, op.state_of_formation,
  op.parcel_count, op.total_assessed_value,
  op.sfr_count, op.mf_count, op.commercial_count,
  op.state_count
FROM PROPRAVEN_MARKETPLACE.GOLD_INTELLIGENCE.OWNER_PORTFOLIO op
WHERE op.primary_state = '06'   -- CA
  AND op.parcel_count >= 100
  AND op.entity_type IN ('LLC', 'CORP', 'LP', 'TRUST')
ORDER BY op.total_assessed_value DESC
LIMIT 100;


-- ----------------------------------------------------------------------------
-- 5. RECENT DEAL FLOW: parcels sold in the last 90 days, $500k+
-- ----------------------------------------------------------------------------
SELECT
  pa.prpv_parcel_id, pa.apn,
  rt.last_sale_date, rt.last_sale_price,
  o.name_raw AS new_owner,
  pr.property_type_class, pr.year_built
FROM PROPRAVEN_MARKETPLACE.GOLD_INTELLIGENCE.PARCEL_RECENT_TRANSFER rt
JOIN PROPRAVEN_MARKETPLACE.SILVER.PARCEL pa                ON pa.prpv_parcel_id = rt.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.SILVER.PROPERTY pr              ON pr.prpv_parcel_id = pa.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.GOLD_GRAPH.EDGE_OWNER_PARCEL eop ON eop.prpv_parcel_id = pa.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.SILVER.OWNER o                  ON o.prpv_owner_id = eop.prpv_owner_id
WHERE rt.is_recent_transfer_90d
  AND rt.confidence_score >= 0.95
  AND rt.last_sale_price >= 500000
  AND pa.state_fips = '48'   -- TX
ORDER BY rt.last_sale_date DESC, rt.last_sale_price DESC
LIMIT 500;


-- ----------------------------------------------------------------------------
-- 6. INSTITUTIONAL TRACKING: top SFR institutional buyers nationally
-- ----------------------------------------------------------------------------
SELECT
  op.name_raw, op.entity_type,
  op.parcel_count,
  op.sfr_count,
  ROUND(100.0 * op.sfr_count / NULLIF(op.parcel_count, 0), 1) AS pct_sfr,
  op.state_count, op.primary_state
FROM PROPRAVEN_MARKETPLACE.GOLD_INTELLIGENCE.OWNER_PORTFOLIO op
WHERE op.entity_type IN ('LLC', 'CORP', 'LP')
  AND op.parcel_count >= 100
  AND op.sfr_count * 1.0 / NULLIF(op.parcel_count, 0) >= 0.7   -- 70%+ SFR
ORDER BY op.sfr_count DESC
LIMIT 50;


-- ----------------------------------------------------------------------------
-- 7. LENDER EXPOSURE: parcels with mortgages from a specific lender
--    (v1.1 — depends on SILVER.LOAN; placeholder query)
-- ----------------------------------------------------------------------------
-- Coming in v1.1 — see methodology doc.


-- ----------------------------------------------------------------------------
-- 8. DATA-FRESHNESS CHECK: when was each entity last refreshed?
-- ----------------------------------------------------------------------------
SELECT
  silver_entity, bronze_table, refresh_cadence,
  last_refreshed_at, source_as_of, row_count
FROM PROPRAVEN_MARKETPLACE.META.SOURCE_LINEAGE
ORDER BY silver_entity;


-- ----------------------------------------------------------------------------
-- 9. CONFIDENCE-FILTERED QUERY: deterministic-only matches
-- ----------------------------------------------------------------------------
SELECT
  pa.prpv_parcel_id, pa.apn,
  o.name_raw, o._match_method, o._confidence_score
FROM PROPRAVEN_MARKETPLACE.SILVER.PARCEL pa
JOIN PROPRAVEN_MARKETPLACE.GOLD_GRAPH.EDGE_OWNER_PARCEL eop ON eop.prpv_parcel_id = pa.prpv_parcel_id
JOIN PROPRAVEN_MARKETPLACE.SILVER.OWNER o                  ON o.prpv_owner_id = eop.prpv_owner_id
WHERE o._match_method = 'deterministic'
  AND o._confidence_score = 1.0
  AND pa.state_fips = '37';


-- ----------------------------------------------------------------------------
-- 10. ENTITY DICTIONARY EXPLORATION: discover the model
-- ----------------------------------------------------------------------------
SELECT entity_name, entity_layer, description, primary_key, expected_row_count
FROM PROPRAVEN_MARKETPLACE.META.ENTITY_DICTIONARY
ORDER BY entity_layer, entity_name;