Union Types

union.sql

# 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