CTE Names

cte-names.sql

# Good: Descriptive names

WITH beacon_interest_contact as (
    SELECT
        email,
        FROM_UNIXTIME(property_beacon_interest/1000) as expressed_interest_at
    FROM
        hubspot.contact
    WHERE
        property_beacon_interest IS NOT NULL),
beacon_interest_conversation as (
    SELECT
        email,
        created_at as expressed_interest_at
    FROM
        helpscout.conversation
    INNER JOIN
        helpscout.conversation_tag
    ON
        conversation.id = conversation_tag.conversation_id
    WHERE
        conversation_tag.tag = 'beacon-interest'),
beacon_interest_union as (
    SELECT
        *
    FROM
        beacon_interest_contact
    UNION
    SELECT
        *
    FROM
        beacon_interest_conversation)
SELECT
    email,
    MIN(expressed_interest_at) as expressed_interest_at
FROM
    beacon_interest_union
GROUP BY
    email;

# Bad: Non-descriptive names

;with x0 as
(
Select  hcv.email, hcv.created_at expressed_interest_at
From helpscout.conversation hcv 
Inner join helpscout.conversation_tag hct on hcv.id = hct.conversation_id
Where hct.tag = ‘beacon-interest’
Union 
Select email, dateadd(S,property_beacon_interest, '1970-01-01') expressed_interest_at
From hubspot.contact 
Where property_beacon_interest is not null

), 

X1 as (
Select email , expressed_interest_at,  row_number() over (partition by email  order by  expressed_interest_at) as RowNo
From x0
Order by expressed_interest_at 
)

Select email, expressed_interest_at
From x1
Where RowNo =1