Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/multiple-interest.sql Secret

Created Nov 12, 2018
Embed
What would you like to do?
Multiple Interest
# Good: Taking the earliest timestamp for each email
SELECT email, MIN(created_at) AS expressed_interest_at
FROM
(SELECT c.email, MIN(created_at) AS created_at
FROM helpscout.conversation c, helpscout.conversation_tag ct
WHERE c.id =ct.conversation_id
AND ct.tag="beacon-interest"
GROUP BY id
UNION
SELECT email,DATETIME(time_created/1000,'unixepoch')|| ' UTC' AS created_at
FROM hubspot.contact
WHERE time_created IS NOT NULL)
GROUP BY email ORDER BY expressed_interest_at;
# Bad: Missing it
select cont.email, cont.property_beacon_interest as expressed_interest_at
from hubspot.contact cont
where cont.property_beacon_interest is not null
union
select conv.email, conv.created_at as expressed_interest_at
from helpscout.conversation conv
join helpscout.conversation_tag tag
on conv.id = tag.conversation_id and tag.tag = 'beacon-interest'
order by expressed_interest_at
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment