snowflake2.sql

snowflake2.sql

SELECT
contact.properties:firstname::string AS contact_firstname,
contact.properties:lastname::string AS contact_lastname,
contact.properties:email::string AS contact_email,
TO_TIMESTAMP_NTZ(contact.properties:notes_last_updated::number, 3) AS contact_last_contacted,
deal.properties:amount::number AS deal_amount,
deal.properties:name::string AS deal_name
FROM v2_live.object_with_object_properties deal
LEFT JOIN v2_live.associations deal_to_contact ON deal.objectId = deal_to_contact.fromObjectId
LEFT JOIN v2_live.object_with_object_properties contact ON contact.objectId = deal_to_contact.toObjectId
WHERE
deal.objecttypeid = '0-3' AND
deal_to_contact.combinedAssociationTypeid = (SELECT combinedAssociationTypeid FROM v2_live.association_definitions WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-1') AND
contact.objectTypeId = '0-1' AND
deal_amount > 10000 AND
contact_last_contacted < DATEADD(month, -1, CURRENT_TIMESTAMP())