Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/casing.sql Secret

Created Nov 12, 2018
Embed
What would you like to do?
SQL Casing
# Good: All uppercase
SELECT
email,
datetime(property_beacon_interest/1000, 'unixepoch') AS expressed_interest_at
FROM
hubspot.contact
WHERE
property_beacon_interest IS NOT NULL
UNION
SELECT
c.email,
c.created_at AS expressed_interest_at
FROM
helpscout.conversation c
INNER JOIN helpscout.conversation_tag ct
ON c.id = ct.conversation_id AND ct.tag = 'beacon-interest'
# Good: All lowercase
select
email,
first_interest = min(first_interest)
from (
-- interest forms
select
email,
first_interest = dateadd(S, property_beacon_interest/1000, '1970-01-01')
from
hubspot.contact
where
property_beacon_interest is not null
-- support team tags
union all
select
email,
first_interest = created_at
from
helpscout.conversation c join
helpscout.conversation_tag ct on c.id = ct.conversation_id and ct.tag = 'beacon-interest'
) combined
group by
email
# Okay: Mixed uppercase and lowercase
SELECT helpscout.conversation.email, helpscout.conversation.created_at as expressed_interest_at
FROM helpscout.conversation
INNER JOIN helpscout.conversation_tag ON helpscout.conversation.id=helpscout.conversation_tag.conversation_id
WHERE tag="beacon-interest"
UNION
select hubspot.contact.email, DATETIME(hubspot.contact.property_beacon_interest/1000, 'unixepoch') || ' UTC' as expressed_interest_at
FROM hubspot.contact
WHERE property_beacon_interest != ''
ORDER BY expressed_interest_at;
# Bad: Other variations
Select
email
From
helpscout.conversation
Where
created_at
And
conversation_id
Order By
email
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment