-
-
Save mattm/e52ea08d64f26d009b3574f74273d244 to your computer and use it in GitHub Desktop.
CTEs vs Subqueries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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