Property Type & Ownership Queries

The following contains example scenarios for isolating properties based on how they are built or owned, rather than loan structure alone. Each scenario includes a sample Cortex Code input and the corresponding SQL example.

Use this scenario to identify recently built homes where the lien was created in the same month as the property’s most recent purchase, which can be useful for builder, lender, or new-construction analysis.

Cortex Code input:
Write an SQL query that returns properties in [insert state here] that were built within the last three years, where the lien1 contract date occurred in the same month as the last close date. Include property details, AVM information, lien1 loan type, lender name, and purchase price.

SQL example:

SELECT

address,

city,

zipcode,

property_type,

year_built,

hc_value_estimate AS avm_value,

last_close_date,

last_close_price,

lien1_loan_type,

lien1_amount,

lien1_contract_date,

lien1_lender_name

FROM

XXXX

WHERE

year_built >= 2022

AND DATE_TRUNC ('MONTH', lien1_contract_date) = DATE_TRUNC ('MONTH', TO_DATE (last_close_date))

AND NOT year_built IS NULL

AND NOT lien1_contract_date IS NULL

AND NOT last_close_date IS NULL

ORDER BY

year_built DESC,

last_close_price DESC;

Use this scenario to identify likely investment properties by comparing the owner’s mailing address to the property address and filtering out owner-occupied homes.

Cortex Code input:
Write an SQL query that identifies properties in [insert state here] where the owner’s mailing address is different from the property address and the property is not marked as owner-occupied. Include property details, last close price, and rental AVM information.

SQL example:

SELECT

address,

owner_address,

city,

zipcode,

property_type,

bedrooms,

bathrooms_total,

year_built,

last_close_price,

hc_value_estimate,

hc_rental_avm_lower,

hc_rental_avm_upper

FROM

XXXXXX

WHERE

owner_address <> address

AND owner_occupied_yn IS NULL

ORDER BY

city,

zipcode;