Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/union.sql Secret

Last active Nov 12, 2018
Embed
What would you like to do?
Union Types
# Good: UNION ALL
SELECT
C.email,
C.created_at AS expressed_interest_at
FROM helpscout.conversation C
INNER JOIN helpscout.conversation_tag CT ON CT.conversation_id = C.id
WHERE CT.tag = 'beacon-interest'
UNION ALL
SELECT
email,
DATEADD(S, CONVERT(INT,LEFT(property_beacon_interest, 10)), '1970-01-01') AS expressed_interest_at
FROM hubspot.contact
# Okay: UNION DISTINCT
SELECT email, MIN(created_at) AS expressed_interest_at
FROM
(SELECT a.email, MIN(created_at) AS created_at
FROM conversation a
INNER JOIN conversation_tag b
ON a.id=b.conversation_id
WHERE b.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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment