Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/ctes.sql Secret

Last active Nov 13, 2018
Embed
What would you like to do?
CTEs vs Subqueries
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment