SQL Screener Answer

sql-answer.sql

with hubspot_interest as (

  select
    email,
    timestamp_millis(property_beacon_interest) as expressed_interest_at
  from hubspot.contact
  where property_beacon_interest is not null

), 

support_interest as (

  select 
    email,
    created_at as expressed_interest_at
  from helpscout.conversation
  join helpscout.conversation_tag on conversation.id = conversation_tag.conversation_id
  where tag = "beacon-interest"

), 

combined_interest as (

  select * from hubspot_interest
  union all
  select * from support_interest

),

final as (

  select 
    email,
    min(expressed_interest_at) as expressed_interest_at
  from combined_interest
  group by 1

)

select * from final