查詢雪花中的HubSpot數據


SELECT
objectId,
properties: dealname:: string AS dealname,
properties: dealstage:: string AS dealstage,
properties: amount:: number AS dealamount
FROM object_with_object_properties
, objectTypeId = '0-3'
LIMIT 3;

SELECT
objectId,
properties: firstname:: string AS firstname,
properties: lastname:: string AS lastname
FROM object_with_object_properties
WHERE object_with_object_properties.objectTypeId = '0-1'
AND objectId IN (
SELECT objectId
FROM object_properties_history
WHERE object_properties_history.objectTypeId = '0-1'
AND object_properties_history.name = 'lifecyclestage'
AND object_properties_history.value = 'subscriber'
);

選擇
contact_objectId ， deal_objectId

從ASSOCIATIONS_CONTACTS_TO_DEAL

其中contactId = 6626541373

選擇contacts.property_firstname CONTACT_FIRSTNAME ，

contacts.property_lastname contact_lastname,
contacts.property_email contact_email,
deals.property_dealtype deal_type, deals.property_amount deal_amount
FROM objects_contact
contacts association_contact_to_deals associated on
assoc.contact_objectid = contacts.objectid
JOIN objects_deals on deals.objectid = assoc.deal_objectid
where contacts.property_hs_timezone = 'america_slash_bogota'

SELECT SUM (Deals.property_hs_closed_amount) AS
TOTAL_CLOSED_AMOUNT, contacts.property_email CONTACT_EMAIL
FROM
OBJECTS_CONTACT CONTACT CONTACTS CONTACTS COLLECTIONS_
TO_DEALS
ASSOCIATIONS.contact_objectid = contacts.objectid CONTACT OBJECTIONS_DEALS
CONTACT_EMAIL ORD
BY TOTAL_CLOSED_AMOUNT DESC

選擇combinationAssociationTypeId ， fromObjectTypeId ， toObjectTypeId ， name
FROM association_definition
order BY category ， id

選擇combinationAssociationTypeId ， fromObjectTypeId ， toObjectTypeId ， name
FROM association_defitions ，
其中fromObjectTypeId = '0-3'和toObjectTypeId = '0-2'
和isMainAssociationDefinition = true
ORDER BY category ， id

從combinationAssociationTypeId = '0-5'的關聯

中選擇combinedAssociationTypeId、fromObjectId、toObjectId

從中
選擇combinationAssociationTypeId = (

從
association
_defitions
中選擇combinedAssociationTypeId ，
其中fromObjectTypeId = '0-1'
和toObjectTypeId = '0-3'
和isMainAssociationDefinition)
和fromObjectId = 4464 ；

從所有者中
選擇ownerId, userId, email, firstname, lastname
WHERE archived = false

從ownerId = 29584574的所有者
中選擇ownerId、userId、
email、firstname、lastname ；

選擇
contact.objectId contactid,
contact.property_firstname contact_firstname,
contact.property_lastname contact_lastname,
contact.property_hubspot_owner_id contact_hubspot_owner_id
, owner.firstname owner_firstname
, owner.lastname owner_lastname
, owner.email owner_email
FROM objects_contact

加入OWNERS OWNERS ON contact.property_hubspot_owner_id = owner.ownerId


選擇objectTypeId, pipelineId, label
FROM pipeline
WHERE objectTypeId = '0-3'
AND not archived
ORDER BY displayorder;


選擇

deals.objectId deal_id,
deals.property_dealname deal_name,
pipelines.label pipeline_name
FROM objects_deals
INNER JOIN pipelines
on deals.objectTypeId = pipelines.objectTypeId
AND deals.property_pipeline = pipelines.pipelineid
WHERE pipelines.objectTypeId = '0-3';



SELECT
Deals.objectId deal_id,
deals.properties: dealname:: varchar deal_name,
pipelines.label pipeline_name
FROM object_with_object_properties deals
INNER JOIN pipelines
on deals.objectTypeId = pipelines.objectTypeId
AND deals.properties: pipeline:: string = pipelines.pipelineid
WHERE deals.objecttypeId = '0-3'
AND pipelines.objectTypeId = '0-3';

選擇pipelineid、stageId、label
FROM pipeline_stages
WHERE not archived
AND pipelineid = (
SELECT pipelineid
FROM pipeline
WHERE objectTypeId = '0-3'
AND not archived
AND label = 'Sales Pipeline' LIMIT 1)
ORDER BY displayorder;


SELECT
deal.objectId deal_id,
deals.properties: dealname:: varchar deal_name,
pipelines.label pipeline_name,
pipeline_stages.label pipeline_stage_name
FROM object_with_object_properties deals
INNER JOIN pipelines
on deals.objectTypeId = pipelines.objectTypeId
AND deals.properties: pipeline:: string = pipelines.pipelineid
INNER JOIN pipeline_stage
on deals.objectTypeId = pipeline_stages.pipelineid
AND deals.properties: pipeline:: string = pipeline_stages.pipelineid
AND deals.properties: dealstage: string = pipeline_stage.stageId
WHERE dealals.objectId = '0-3';

選擇
click.occuredat click_timestamp,
click.property_hs_click_raw_url click_url,
contact.properties: firstname:: varchar contact_firstname,
contact.properties: lastname:: varchar contact_lastname
FROM events_clicked_link_in_email_V2 click
LEFT JOIN object_with_object_properties contact
on click.objectTypeId = contact.objectTypeId
AND click.objectId = contact.objectId
LIMIT 3;

選擇

contact.objectId as objectid,
contact.property_firstname as firstname,
contact.property_lastname as lastname
FROM objects_contact contact
INNER JOIN list_memberships on contact.objectId = list_memberships.objectId
INNER JOIN list on list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021'


從PROPERTY_DEFINIONS中
選擇*

其中label = [column_name]

選擇campaigns.property_hs_name campaign_name,
forms.property_hs_form_id form_id
FROM objects_campaign
association_campaign_to_form assoc on
assoc.campaign_objectid = campaigns.objectid
JOIN objects_formons on form.objectid = assoc.form_objectid
GROUP BY campaign_name