CTEs vs Subqueries

ctes.sql

# GOOD: CTEs
# I'd put each CTE on a new line though to make it easier to comment and copy/paste

with form as (

    select 
        email,
        to_timestamp(property_beacon_interest) as expressed_interest_at
    from hubspot.contact

), support as (

    select 
        c.email,
        c.created_at as expressed_interest_at
    from hubspot.conversation c
    left join hubspot.conversation_tag t on c.id = t.conversation_id
    where t.tag = 'beacon-interest'

), unioned as (

select * from form
union all
select * from support

), final as (

select 
    email,
    min(expressed_interest_at) as expressed_interest_at
from unioned
group by 1
order by 2

)

select * from final

# OKAY: Subqueries

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