mattm/responses.sql Secret

## responses.sql
# 1

SELECT email,
  min(create_time_at)
FROM (
  SELECT
    email,
    FROM_UNIXTIME(property_beacon_interest/1000) create_time_at
  FROM hubspot_contact
  UNION
  SELECT
    email,
    created_at
  FROM helpscout_conversation
  INNER JOIN helpscout_conversation_tag
    on id = conversation_id
    WHERE tag = 'beacon-interest' ) combined_emails
GROUP BY
email
ORDER BY 2;

# 2

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;

# 3

SELECT email, MIN(created_at) AS expressed_interest_at
FROM

(SELECT a.email, MIN(created_at) AS created_at
FROM helpscout.conversation a
INNER JOIN helpscout.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
;

# 4

WITH all_interest AS (
      SELECT email,
             created_at as expressed_interest_at
        FROM helpscout.conversation c
        JOIN helpscout.conversation_tag ct
          ON c.id = ct.conversation_id
       WHERE tag = 'beacon-interest'
   UNION
      SELECT email,
             TO_DATE('19700101', 'yyyymmdd') + ((property_beacon_interest/1000)/24/60/60) AS expressed_interest_at
        FROM hubspot.contact
 )
 SELECT email, to_char(min(expressed_interest_at), 'YYYY-MM-DD HH24:MI:SS') || ' UTC' as expressed_interest_at
   FROM all_interest
GROUP BY email;

# 5

SET time_zone = '+00:00';
SELECT email, date_format(min(expressed_interest_at),'%Y-%m-%d %H:%i:%s UTC') AS expressed_interest_at
FROM (
  (SELECT email, created_at AS expressed_interest_at
  FROM `helpscout.conversation` AS conv
  INNER JOIN `helpscout.conversation_tag` AS tag ON conv.id = tag.conversation_id
  WHERE tag.tag = "beacon-interest")
  UNION
  (SELECT email, FROM_UNIXTIME(property_beacon_interest/1000) AS expressed_interest_at
  FROM `hubspot.contact` AS contact
  WHERE property_beacon_interest IS NOT NULL)
) AS summary
GROUP BY email
ORDER BY expressed_interest_at, email;

# 6

--METHOD 1 (Intermediate tables and union them together)
----------------------------------------------------------
--union interest form and chat interests together
select
  email
  , created_at
from #hubspot_beacon_interest_property

union all

select
  email
  , created_at
from #hubspot_conversation conv
left join #hubspot_conversation_tag tag on conv.id = tag.conversation_id
where tag like '%beacon-interest%'
order by created_at asc

--METHOD 2 (inner query)
-----------------------------------------
--set the start date time for millisecond calcuation
declare @StartDateTime datetime = '1970-01-01 00:00:00'


select
  case when conv.email is null then prop.email else conv.email end as email
  , case when conv.created_at is not null then conv.created_at else prop.created_at end as created_at
from #hubspot_conversation conv
inner join #hubspot_conversation_tag tag on conv.id = tag.conversation_id and tag.tag = 'beacon-interest'
full outer join (
    select
      email
      , created_at
    from #hubspot_beacon_interest_property
  )prop on conv.created_at = prop.created_at where conv.created_at is null or prop.created_at is null
order by created_at asc

# 7

SELECT
  C.email,
  C.created_at AS expressed_interest_at
FROM helpscout.conversation C
INNER JOIN helpscout.conversation_tag CT ON CT.conversation_id = C.id
WHERE CT.tag = 'beacon-interest'
UNION ALL
SELECT
  email,
  DATEADD(S, CONVERT(INT,LEFT(property_beacon_interest, 10)), '1970-01-01') AS expressed_interest_at
FROM hubspot.contact

#8

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

# 9

select
  email
  , created_at as “expressed_interest_at”
from helpscout.conversation c
left join helpscout.conversation_tag ct on c.id=ct.tag
where tag = ‘beacon-interest’
Union
select
  email
  , timestamp 'epoch' + CAST(created_at AS BIGINT)/1000 * interval '1 second' as “expressed_interest_at”
from hubspot.contact
where created_at is not null

# 10

SELECT hubspot.contact.email, datetime(hubspot.contact.property_beacon_interest/1000, 'unixepoch') As expressed_interest_at
FROM hubspot.contact
WHERE hubspot.contact.property_beacon_interest IS NOT NULL
UNION
SELECT helpscout.conversation.email, helpscout.conversation.created_at
FROM helpscout.conversation
INNER JOIN helpscout.conversation_tag
ON helpscout.conversation_tag.conversation_id = helpscout.conversation.id
WHERE helpscout.conversation_tag.tag = 'beacon-interest'
ORDER BY expressed_interest_at

# 11

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

# 12

SELECT  email, MIN(expressed_interest_at) AS expressed_interest_at
FROM
    (SELECT  email, CAST(from_unixtime(property_beacon_interest) AS DATETIME) as expressed_interest_at
    FROM    hubspot.contact
    WHERE   property_beacon_interest IS NOT NULL

    UNION

    SELECT  a.email, a.created_at as expressed_interest_at
    FROM    helpscout.conversation as a
            INNER JOIN helpscout.conversation_tag as b
            on a.id = b.conversation_id
            and b.tag = 'beacon-interest') AS alldata
GROUP BY email;

# 13

SELECT DISTINCT email,
        min(expressed_interest_at)
FROM
(SELECT   email,
            created_at AS expressed_interest_at
FROM          helpscout.conversation c
JOIN    helpscout.conversation_tag ct
  ON c.id = ct.conversation_id AND ct.tag = ‘beacon-interest’

UNION ALL

SELECT  email,
    dateadd(s, property_beacon_interest, ‘19700101’) AS expressed_interest_at
FROM  hubspot.contact
WHERE   property_beacon_interest IS NOT NULL) AS full_select

# 14

with interest_form as (

  select
    email,
    dateadd(s, convert(bigint,  property_beacon_interest) / 1000, convert(datetime, '1-1-1970 00:00:00')) as expressed_interest_at
  from hubspot.contact

), conversations_beacon as (

  select
    email,
    created_at::datetime as expressed_interest_at
  from helpscout.conversation_tag as t
  left join helpscout.conversation as c on t.id = c.id
  where tag = 'beacon-interest'


), joined as (

  select
    coalesce (c.email, i.email) as email,
    coalesce (c.expressed_interest_at, i.expressed_interest_at::varchar) as expressed_interest_at
  from conversations_beacon as c
  full outer join interest_form as i on c.email = i.email

) select * from joined

# 15

SELECT a.email, min(b.created_at) AS expressed_interest_at
FROM hubspot.contact AS a
JOIN helpscout.conversation AS b ON a.email = b.email
GROUP BY a.email

# 16

SELECT a.email, a.created_at as tag
FROM helpscoutConversation a
WHERE id IN (2,4)
UNION
SELECT b.email,  FROM_UNIXTIME(b.property_beacon_interest) as tag
FROM hubspotContact b
WHERE b.property_beacon_interest IS NOT NULL;

# 17

SELECT c.email AS email, to_timestamp( c.property_beacon_interest) AS expressed_interest_at
FROM hubspot.contact
WHERE c.property_beacon_interest IS NOT NULL
UNION
SELECT a.email AS email, a.created_at AS expressed_interest_at
FROM helpscout.conversation AS a
INNER JOIN helpscout.conversation AS b ON a.id = b.conversation_id
WHERE b.conversation_id = 'beacon-interest'

# 18

Select
  hc.email,
  c.[property_beacon_interest] as expressed_interest_at
  from [dbo].[helpscout_conversation] hc
  inner join dbo.helpscout_conversation_tag hct on hc.id = hct.conversation_id
  inner join [dbo].[hubspot_contact] c on c.email = hc.email and c.[property_beacon_interest] is not null
where
hct.tag = 'beacon-interest'

# 19

SELECT A.email, min(A.expressed_interest_at) expressed_interest_at
  FROM (    SELECT email,
      DATEADD(MILLISECOND,property_becon_interest % 1000, DATEADD(SECOND, property_becon_interest / 1000, '19700101')) EXPRESSED_INTEREST_AT
    FROM hubspot.contact
    WHERE property_becon_interest is not null
  UNION
    SELECT EMAIL, CREATED_AT as EXPRESSED_INTEREST_AT
      FROM HELPSCOUT.CONVERSATION
    WHERE ID IN(
            SELECT CONVERSATION_ID    FROM HELPSCOUT.CONVERSATION_TAG
            WHERE TAG = 'BEACON-INTEREST'
          )
    ) AS A
GROUP BY EMAIL

# 20

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

  # 21

  WITH total_email AS
(
select email
  ,  dateadd(s, property_beacon_interest / 1000, ‘1970-01-01 00:00:00’) as expressed_interest_at
from hubspot.contact
where property_beacon_interest is not null
)
UINON ALL
(
select a.email
  ,  a.created_at as expressed_interest_at
from helpscout.conversation a
  INNER JOIN
  helpscout.conversation_tag b
  ON a.id = b.conversation_id
where b.tag = ‘beacon-interest’
)
select distinct
   email
  ,MIN(expressed_interest_at) as expressed_interest_at
from total_email
group by 1

# 22

SELECT
  allinterested.email
  ,MIN(allinterested.date) AS expressed_interest_at
FROM
  (
    (SELECT
      cont.email
      ,FROM_UNIXTIME(property_beacon_interest/1000) AS date
    FROM contact cont
    WHERE cont.property_beacon_interest IS NOT NULL)
    UNION ALL
    (SELECT
      conv.email
      ,created_at AS date
    FROM conversation conv
    INNER JOIN conversation_tag tag ON tag.conversation_id = conv.id
    WHERE tag.tag = 'beacon-interest')
  ) AS allinterested
GROUP BY allinterested.email

# 23

select a.email, a.created_at where  a inner join  helpsout.conversation b on (a.id=b.conversation_id) where b.tag=beacon-interest

# 24

SELECT h.email, h.created_at, ht.tag
FROM helpscout.conversation h, helpscout.conversation_tag ht
WHERE ht.tag = "beacon-interest";

# 25

select*from interested_customers;
select * from interested_customers order by expressed_interest_at;
select*from interested_customers where id in (1,2,4)

# 26

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'

# 27

select `helpscout.conversation`.email,ifnull(from_unixtime(property_beacon_interest),`helpscout.conversation`.created_at)  as expressed_interest_at from `helpscout.conversation`
left join `hubspot.contact` on `hubspot.contact`.email=`helpscout.conversation`.email
inner join `helpscout.conversation_tag` on `helpscout.conversation`.id=`helpscout.conversation_tag`.conversation_id
where `helpscout.conversation_tag`.tag='beacon-interest' or property_beacon_interest is not null
group by expressed_interest_at

# 28

;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

# 29

SELECT email, DATEDIFF(MILLISECOND, 0,property_beacon_interest) as expressed_interest_at
FROM helpscout.conversation
INNER JOIN helpscout.conversation_tag
WHERE id.helpscout.conversation = conversation_id.helpscout.conversation_tag

# 30

SELECT email, datetime(substr(property_beacon_interest,1,10), 'unixepoch') as expressed_interest_at_UTC
FROM hubspot_contact
WHERE property_beacon_interest IS NOT NULL

UNION

SELECT email, substr(created_at,1,length(created_at)-4) as created_at
FROM (SELECT *
FROM helpscout_conversation as a1
JOIN helpscout_conversation_tag as a2
ON a1.id = a2.conversation_id
WHERE a2.tag = 'beacon-interest') as join_table
ORDER BY expressed_interest_at_UTC

# 31

select
  email,
  CONVERT(DATETIME,[property_beacon_interest]/ 10000.0/1000/86400-693595) as expressed_interest_at
from hubspot.contact
where [property_beacon_interest] is not null

union

select
  email,
  CONVERT(DATETIME,[created_at]) as expressed_interest_at
from helpscout.conversation
where id in (2,4)

# 32

SELECT hubspot.contact.email, helpscout.conversation.created_at AS expressed_interes_at
FROM hubspot.contact
INNER JOIN helpscout.conversation ON hubspot.contact.email=helpscout.conversation.email
INNER JOIN helpscout.conversation_tag ON helpscout.conversation.id=helpscout.conversation_tag.conversation_id
WHERE helpscout.conversation_tag.tag='new-trial'

# 33

with resultset1 as(
select email,
  to_char(to_timestamp(property_beacon_interest / 1000),'YYYY-MM-DD HH24:MI:SS') as expressed_interest_at
from hubspot_contact
where property_beacon_interest is not null
union all
select helpscout_conversation.email,
  to_char(helpscout_conversation.created_at,'YYYY-MM-DD HH24:MI:SS')  as expressed_interest_at
from helpscout_conversation
left join helpscout_conversation_tag on helpscout_conversation.id = helpscout_conversation_tag.conversation_id
where helpscout_conversation_tag.tag = 'beacon-interest'
), resultset2 as(
select email,expressed_interest_at,row_number() over(partition by email ORDER BY expressed_interest_at) as row_number
from resultset1
)
select email, expressed_interest_at
from resultset2
where row_number = 1

# 34

Select a.email, min(a.created_at) as expressed_interest_at FROM
(Select  hubspot.dbo.contact.email, hubspot.dbo.contact.created_at FROM hubspot.dbo.contact
Inner join helpscout.dbo.conversation_tag on hubspot.dbo.contact.id  = helpscout.dbo.conversation_tag.id WHERE helpscout.dbo.conversation_tag.tag ='beacon-interest'
Union
Select hubspot.dbo.contact.email, DATEADD(MILLISECOND, hubspot.dbo.property_beacon_interest % 1000, DATEADD(SECOND, hubspot.dbo.property_beacon_interest / 1000, '19700101')) as created_at FROM
hubspot.dbo.contact WHERE hubspot.dbo.property_beacon_interest is not null)a
GROUP BY a.email

# 35

SELECT * FROM `hubspot.contact` as n,
`helpscout.conversation` as m WHERE m.id IN ( SELECT conversation_id
FROM `helpscout.conversation_tag` WHERE tag='beacon-interest') and
n.property_beacon_interest IS NOT NULL and n.email!=m.email ;

# 36

SELECT email, LEAST(FROM_UNIXTIME(property_beacon_interest), [helpscout.conversation].created_at) AS expressed_interest_at
FROM (
  SELECT *
  FROM [helpscout.conversation_tag] hct
  JOIN [helpscout.conversation] hc
  ON hct.conversation_id = hc.id
  WHERE hct.conversation_id = 'beacon-interest') AS t1
)
INNER JOIN t1
ON [helpscout.conversation].email = [hubspot.contact].email
GROUP BY 1
ORDER BY 2 DESC;

# 37

SELECT
email as email,
TO_DATE('19700101','yyyy-mm-dd') + ((property_beacon_interest/1000)/24/60/60) expressed_interest_at from dual;
FROM
hubspot.contact;
WHERE
property_beacon_interest IS NOT NULL;
UNION
SELECT
email as email, created_at as expressed_interest_at;
FROM helpscout.conversation;
WHERE id IN (SELECT conversation_id
FROM helpscout.conversation_tag
WHERE tag equals beacon-interest);

# 38

SELECT
email as email,
TO_DATE('19700101','yyyy-mm-dd') + ((property_beacon_interest/1000)/24/60/60) expressed_interest_at from dual;
FROM
hubspot.contact;
WHERE
property_beacon_interest IS NOT NULL;
UNION
SELECT
email as email, created_at as expressed_interest_at;
FROM helpscout.conversation;
WHERE id IN (SELECT conversation_id
FROM helpscout.conversation_tag
WHERE tag equals beacon-interest);

# 39

select C.email, min(C.expressed_interest_at) as expressed_interest_at
from helpscout.conversation_tag CT
  left join helpscout.conversation C
    on CT.conversation_id = C.id
where CT.tag = 'beacon-interest'
group by C.email
order by 2

# 40

select email_1 email, coalesce(case when created_at < property_beacon_interest then created_at else from_unixtime(property_beacon_interest) end, created_at, from_unixtime(property_beacon_interest))  expressed_interest_at from (
select conversation.email email_1, conversation.created_at, conversation_tag.conversation_id, conversation_tag.tag
from conversation left join conversation_tag
on conversation_tag.conversation_id = conversation.id) convers
left join contact on convers.email_1 = contact.email
where convers.conversation_id not null
group by 1

# 41

CREATE TABLE hubspot.contact (
                   email TEXT,
                   property_beacon_interest INTEGER
);

1 - CREATE TABLE helpscout.conversation (
                    id INTEGER,
                    email TEXT,
                    created_at DATETIME
);

2 – CREATE TABLE helpscout.conversation_tag(
                      Conversation_id INTEGER,
                       Tag TEXT
);


SELECT *
FROM helpscout.conversation_tag
WHERE tag = beacon-interest;

From this result we can interpretate which conversation id, the result was beacon-interest

3 – CREATE TABLE results (
                       id INTEGER,
                    email TEXT,
                    created_at DATETIME
),

FROM results
WHERE id = 2;

FROM results
WHERE id = 4;

ALTER TABLE results
DROP COLUMN id;

EXEC sp_RENAME “results.created_at “, “express_interest_at”, 'COLUMN'

# 42

SELECT
         email, FORMAT(DATEADD(‘s’, (property_beacon_interest/1000), ‘1970-01-01 00:00:00’), ‘yyyy-mm-dd hh:nn:ss” UTC”’) AS expressed_interest_at
FROM
  hubspot.contact hsc
WHERE
  property_beacon_interest <> NULL
UNION
SELECT
  hc.email, FORMAT(created_at, ‘yyyy-mm-dd hh:nn:ss” UTC”’) AS expressed_interest_at
FROM
  helpscout.conversation hc
INNER_JOIN
  helpscout.conversation_tag hct ON hc.id = hct.conversation_id
WHERE
        hct.tag = ‘beacon-interest’
ORDER BY
        expressed_interest_at;

# 43

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;

# 44

SELECT HSC.`email`, DATE_FORMAT(FROM_UNIXTIME(HSC.`property_beacon_interest`), '%Y-%m-%e %T UTC') AS expressed_interest_at
FROM `hubspot.contact` AS HSC
WHERE HSC.`property_beacon_interest` IS NOT NULL
UNION
SELECT HC.`email`, DATE_FORMAT(HC.`created_at`, '%Y-%m-%e %T UTC') AS expressed_interest_at
FROM `helpscout.conversation` AS HC
JOIN `helpscout.conversation_tag` AS HCT ON HC.`id`=HCT.`conversation_id` WHERE HCT.`tag`='beacon-interest'
ORDER BY expressed_interest_at

# 45

SELECT email, FROM_UNIXTIME (property_beacon_interest)
FROM hubspot.contact
WHERE property_beacon_interest IS NOT NULL
UNION
SELECT t1.email, t1.created_at
FROM helpscout.conversation t1
WHERE t1.id
IN
(SELECT t2.id
 FROM helpscout.conversation_tag t2
 WHERE t2.tag = "beacon-interest")

 # 46

 USE teste_helpscout;
SELECT email, data
FROM
  (
  SELECT email, from_unixtime(floor(property_beacon_interest/1000)) as data
  FROM `hubspot.contact`
  WHERE `hubspot.contact`.property_beacon_interest is not null

  UNION

  SELECT email, created_at as data
  FROM `helpscout.conversation`
    INNER JOIN `helpscout.conversation_tag` ON `helpscout.conversation`.id = `helpscout.conversation_tag`.conversation_id
  ) dados
GROUP BY email
ORDER BY data ASC;

# 47

SELECT *
FROM
(
  SELECT
    email,
    CASE WHEN beacon_interest is NULL THEN created_at ELSE CASE WHEN created_at < beacon_interest THEN created_at ELSE beacon_interest END END AS mindate
  FROM
  (
    SELECT
      cv.id,
      cv.email,
      cv.created_at,
      to_timestamp(cn.property_beacon_interest / 1000) AS beacon_interest ,
      ROW_NUMBER () OVER (PARTITION BY cv.email ORDER BY cv.created_at) AS rn
    FROM helpscout.conversation AS cv
    LEFT JOIN hubspot.contact cn ON cn.email = cv.email
  ) AS t
WHERE t.rn=1 AND (SELECT COUNT(*) FROM helpscout.conversation_tag cvt WHERE cvt.conversation_id = t.id) > 0
) AS t2
ORDER BY mindate

# 48

WITH conversations
     AS (SELECT DISTINCT B.email,
                         B.created_at
         FROM   (SELECT *
                 FROM   [dbo].[helpscout.conversation_tag]
                 WHERE  tag = 'beacon-interest') AS A
                LEFT JOIN (SELECT *
                           FROM   [dbo].[helpscout.conversation]) AS B
                       ON A.conversation_id = B.id),
     result
     AS (SELECT email,
                Dateadd(s, property_beacon_interest / 1000, '1970-01-01') AS
                created_at
         FROM   [dbo].[hubspot.contact]
         UNION ALL
         SELECT *
         FROM   conversations)

SELECT email,
       Min(created_at) AS expressed_interest_at
FROM   result
GROUP  BY email

# 49

select email ,min(created_as) from
(
Select email,created_at AS expressed_interest_at
from helpscout.conversation
where email in (select email from property_beacon_interest))
groupby email

# 50

;with

t1
(
email
,contactdate
)
as
(
select
email
,dateadd(s, convert(int,left(property_beacon_interest,10)), '1970-01-01')
from hubspot.contact
,t2
(
email
,convdate
)
as
(
select
email
,created_at
from helpscout.[conversation] cv inner join helpscout.conversation_tag t
on cv.id = t.conversation_id
union all
select
email
,contactdate
from t1
)

select
email
,min(convdate) as expressed_interest_at
from t2
group by email
order by expressed_interest_at

# 51

SELECT email, MIN(interest_time) as expressed_interest_at
FROM
(SELECT email, FROM_UNIXTIME(property_beacon_interest) as interest_time
FROM hubspot.contact
UNION
SELECT email, min(created_at) as interest_time
FROM helpscout.conversation c
INNER JOIN helpscout.conversation_tag ct
ON c.id = ct.conversation_id
WHERE ct.tag = 'beacon-interest')
ORDER BY 2

# 52

select email, min(expressed_interest_at) as expressed_interest_at
from ( select email, min(from_unixtime(property_beacon_interest)) as expressed_interest_at
           from hubspot.contact
           where property_beacon_interest is not NULL
           group by email
           UNION
           select hc.email as email, min(hc.created_at) as expressed_interest_at
           from helpscout.conversation as hc INNER JOIN helpscout.conversation_tag as hc_tag
           ON hc.id = hc_tag.conversation_id
           where hc_tag.tag = 'beacon_interest'
           group by hc.email)
group by email
order by expressed_interest_at asc;

# 53

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;

# 54

SELECT email, min(expressed_interest_at) FROM (

SELECT contact.email, TO_TIMESTAMP(contact.property_beacon_interest/1000) as expressed_interest_at FROM contact WHERE contact.property_beacon_interest is not null UNION ALL
SELECT conversation.email, conversation.created_at as expressed_interest_at FROM conversation WHERE conversation.id = ANY(SELECT conversation_tag.conversation_id FROM conversation_tag WHERE conversation_tag.tag = 'beacon-interest')

) as temp GROUP BY email

# 55

SELECT DISTINCT c.email, [expressed_interest_at]=MIN(c.created_at)
FROM
(SELECT email, created_at
FROM conversation
UNION
SELECT email,
[created_at]=CONCAT(CONVERT(DATETIME,DATEADD(S,SUBSTRING(property_beacon_interest,1,10), '1970-01-01 00:00:00'),120),' UTC')
FROM contact
WHERE ISNULL(property_beacon_interest,'')!='') c
JOIN conversation_tag t ON c.id=t.conversation_id
WHERE t.tag='beacon-interest'
GROUP BY email
ORDER BY expressed_interest_at;

# 56

WITH combined_tb AS
(SELECT email,
               (CONVERT(varchar(20), DATEADD(millisecond, property_beacon_interest,
                                    CONVERT(datetime, ‘1970-01-01’, 120)) AT TIME ZONE ‘UTC’, 120) + ‘ UTC’)
                AS created_at
FROM hubspot.contat
UNION
SELECT email, created_at
FROM helpscout.conversation AS con LEFT JOIN helpscout.conversation_tag AS tag
ON con.id = tag.conversation_id
WHERE tag.tag = ‘beacon-interest’)

# Next, find out the first time each user expressed interest using window function and display the final outcome

SELECT email, created_at
FROM
(SELECT email, created_at, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rank
FROM combined_tb) AS temp
WHERE rank = 1

# 57

SELECT EMAIL "email", MIN(CREATED_AT) "expressed interest at"
 FROM
(SELECT
  A.EMAIL,
  TO_CHAR(TIMESTAMP '1970-01-01 00:00:00.000' + NUMTODSINTERVAL( A.PROPERTY_BEACON_INTEREST / 1000, 'SECOND' ) , 'MM-DD-YYYY HH24:MI:SS') || ' UTC'
         AS CREATED_AT
FROM CONTACT A
WHERE A.PROPERTY_BEACON_INTEREST IS NOT NULL
UNION
SELECT
  B.EMAIL,
 TO_CHAR(B.CREATED_AT,'MM-DD-YYYY HH24:MI:SS') || ' UTC'
FROM CONVERSATION B )
GROUP BY EMAIL
ORDER BY MIN(CREATED_AT)

# 58

SELECT  email,  MIN(created_at)  FROM  helpscout.conversation
WHERE ID IN (SELECT CONVERSATION_ID FROM helpscout.conversation_tag )
GROUP BY EMAIL
UNION
SELECT EMAIL, property_beacon_interest  FROM hubspot.contact

# 59

select a.email, a.created_at, DATEADD(S, c.[property_beacon_interest], '1970-01-01')as dd  from [dbo].[helpscout.conversation] a
 JOIN [dbo].[helpscout.conversation_tag] b
 ON a.id = b.conversation_id
INNER JOIN [dbo].[hubspot.contact] c ON a.email = c.email
 where b.tag in ('new-trial', 'beacon-interest')

 # 60

 SELECT DISTINCT email, created_at as expressed_interest_at FROM `helpscout.conversation` JOIN `helpscout.conversation_tag` ON `helpscout.conversation`.id=`helpscout.conversation_tag`.conversation_id

 # 61

 Select
   email
From
   helpscout.conversation
Where
   created_at
And
  conversation_id
Order By
  email

# 62

SELECT helpscout.conversation.id, email, created_at helpscout.conversation_tag.conservation_id, tag
FROM helpscout.conversation_tag
INNER JOIN HelpScout.conversation
ON HelpScout.conversation.email, created_at=helpscout.conversation_tag.conservation_id

# 63

SELECT DISTINCT email, expressed_interest_at
FROM helpscout.conversation AS CONV
JOIN helpscout.conversation_tag AS TAG ON CONV.ID = TAG.CONVERSATION_ID
WHERE TAG.TAG = "beacon-interest"

# 64

 SELECT cnv.email,
                cnv.created_at AS expressed_interest_at
    FROM helpscout.conversation AS cnv
       JOIN helpscout.conversation_tag AS ctg
          ON cnv.id = ctg.conversation_id
   WHERE ctg.tag = 'beacon-interest'
    UNION
    SELECT email,
                 TO_TIMESTAMP(property_beacon_interest / 1000) AT TIME ZONE 'UTC'
                 AS expressed_interest_at
       FROM hubspot.contact
     WHERE property_beacon_interest IS NOT NULL
ORDER BY expressed_interest_at

# 65

SELECT email, MIN(created_at) AS expressed_interest_at
FROM
(SELECT c.email, MIN(created_at) AS created_at
FROM helpscout.conversation c, helpscout.conversation_tag ct
WHERE c.id =ct.conversation_id
AND ct.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;

# 66

SELECT hubspot.contact.email, MIN(hubspot.contact.property_beacon_interest) as expressed_interest_at
FROM hubspot.contact
WHERE = hubspot.contact.property_beacon_interest != NULL
GROUP_BY   hubspot.contact.email
UNION
SELECT helpscout.conversation.email, MIN(helpscout.conversation.created_at)(S, [unixtime], '1970-01-01')
as expressed_interest_at
FROM helpscout.conversation
GROUP_BY   helpscout.conversation.email
JOIN helpscout.conversation_tag
  on helpscout.conversation.id = helpscout.conversation_tag.id
WHERE   = helpscout.conversation_tag.tag = ‘beacon-interest’);

# 67

Select email, created_at from helpscout.conversation where helpscout.conversation.id = helpscout.conversation_tag.conversation_id and tag in ('new-trial', 'beacon-interest')

# 68

--DROP FUNCTION dbo.fn_UNIX_to_UTC;
CREATE FUNCTION dbo.fn_UNIX_to_UTC(@UNIX BIGINT)
RETURNS VARCHAR(25)
AS
BEGIN
  RETURN (SELECT CONVERT(varchar, DATEADD(S, @UNIX/1000, '1970-01-01'), 120)+ ' UTC')
END;
GO

WITH total_interested
AS
(SELECT cont.email AS email,
    dbo.fn_UNIX_to_UTC(cont.property_beacon_interest) AS expressed_interest_at
  FROM hubspot.contact cont
  WHERE cont.property_beacon_interest IS NOT NULL
UNION
SELECT conv.email AS email,
     CAST(conv.created_at AS varchar) AS expressed_interest_at
  FROM helpscout.conversation conv
  INNER JOIN helpscout.conversation_tag conv_tag on
        conv.id = conv_tag.conversation_id
  WHERE conv_tag.tag = 'beacon-interest')

SELECT filtered.email as email, filtered.expressed_interest_at as expressed_interest_at
FROM(
  SELECT *,
  ROW_NUMBER() OVER (PARTITION BY email ORDER BY expressed_interest_at ASC) AS first_rec
  FROM total_interested) filtered
ORDER by 2

# 69

select email, min(created_at) as expressed_interest_at
from
( select email, created_at
  from helpscout.conversation con
  inner join helpscout.conversation_tag ctag
  on con.id = ctag.conversation_id
  and ctag.tag = 'beacon_interest'
UNION
 (set time_zone = 'UTC'
  select email, from_unixtime(left(property_beacon_interest, 10)) as created_at
  from hubspot.contact
where property_beacon_interest is not null))
group by email
order by expressed_interest_at

# 70

select cont.email, cont.property_beacon_interest as expressed_interest_at
from hubspot.contact cont
where cont.property_beacon_interest is not null
union
select conv.email,  conv.created_at as expressed_interest_at
from helpscout.conversation conv
join helpscout.conversation_tag tag
on conv.id = tag.conversation_id and tag.tag = 'beacon-interest'
order by expressed_interest_at

# 71

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

# 72

SELECT helpscout.conversation.email, created_as AS "expressed_interest_at"
FROM hubspot.contact
JOIN helpscout.conversation ON hubspot.contact.email = helpscout.conversation.email
JOIN helpscout.conversation_tag ON id = conversation_id
WHERE property_beacon_interest IS NOT NULL OR tag = "beacon-interest"

# 73

SELECT email,
    from_unixtime(property_beacon_interest/1000) AS 'expressed_interest_at'
FROM hubspot.contact
WHERE property_beacon_interest IS NOT NULL

UNION ALL

SELECT c.email,
    c.created_at AS 'expressed_interest_at'
FROM helpscout.conversation_tag ct
INNER JOIN helpscout.conversation c ON c.id = ct.conversation_id
WHERE ct.tag = 'beacon-interest';

# 74

WITH results1 AS (
  SELECT    [email], MIN(DATEADD(MILLISECOND, property_beacon_interest % 1000, DATEADD(SECOND, property_beacon_interest / 1000, '19700101'))) AS ContactDate
  FROM    [hubspot].[contact]
  WHERE   property_beacon_interest IS NOT NULL
  GROUP BY  email
)
,results2 AS (
  SELECT    [email], MIN([created_at]) AS ContactDate
  FROM    [helpscout].[conversation]
  INNER JOIN  helpscout.conversation_tag ON [conversation].id = conversation_tag.conversation_id
  WHERE   tag = 'beacon-interest'
  GROUP BY  email
)
SELECT    email, MIN(ContactDate) as expressed_interest_at
FROM    results1
GROUP BY  email
UNION
SELECT    email , MIN(ContactDate) as expressed_interest_at
FROM    results2
GROUP BY  email
ORDER BY  expressed_interest_at

# 75

select email, min(created_at) as expressed_interest_at
(select email, created_at from helpscout.conversation
from helpscout.conversation hc inner join helpscout.conversation_tag ht
hc.id on ht.conversation_id
where tag = 'beacon-interest'

union

select email, (timestamp '1970-01-01 00:00:00 UTC' +
   numtodsinterval(property_beacon_interest/1000, 'SECOND'))
   at time zone 'utc') as created_at
from hubspot.contact
)

group by email

# 76

SELECT email, created_at AS 'expressed_interest_at'
FROM helpscout.conversation
WHERE id IN (
  SELECT conversation_id
  FROM helpscout.conversation_tag
  WHERE tag = 'beacon-interest')
UNION
SELECT email, DATEADD(s, CONVERT(int,LEFT(property_chat_interest, 10)), '1970-01-01') --assuming property_beacon_interest is stored as BIGINT
FROM hubspot.contact
WHERE property_beacon_interest IS NOT NULL

# 77

   WITH helpscout_question AS (
SELECT email,
               TO_TIMESTAMP(property_beacon_interest / 1000) at time zone 'UTC' AS created_at,
   FROM hubspot.contact
 WHERE property_beacon_interest IS NOT NULL

 UNION

SELECT c.email,
               c.created_at
  FROM hubspot.conversation AS c
    JOIN  helpscout.conversation_tag AS t ON c.id = t.conversation_id AND t.tag = 'beacon-interest'
                       )

SELECT email,
              MIN(created_at) AS expressed_interest_at
 FROM helpscout_question
GROUP BY email

# 78

SELECT email
    ,created_at AS expressed_interest_at
INTO ##tmp_helpscout
FROM helpscout.conversation a
JOIN helpscout.conversation_tag b
ON a.conversation_id = b.id
WHERE tag = 'beacon-interest'

-- Formula for time modified from here: https://www.ibm.com/developerworks/community/blogs/SterlingB2B/entry/SQL_Server_Convert_a_Unix_epoch_time_string_to_a_human_readable_date_time_field?lang=en
-- -4 after HH is to adjust to EST
SELECT email
    ,DATEADD(HH, -4, (DATEADD(SS, CONVERT(bigint, SUBSTRING(property_beacon_interest, 1,10)), '19700101'))) AS expressed_interest_at
INTO ##tmp_hubspoty
FROM hubspot.contact
WHERE a.property_beacon_interest IS NOT NULL

SELECT email
    ,MIN(expressed_interest_at) AS expressed_interest_at
FROM (
SELECT * FROM ##tmp_helpscout
UNION ALL
SELECT * FROM ##tmp_hubspoty)
GROUP BY email
ORDER BY 1

# 79

Select
email
, max(created_At) as expressed_interest_at
from(
select hc.email
,hc.created_At
from
helpscout.conversation hc
join helpscout.conversation_tag hct
on hc.id=hct.conversation_id
where tag like '%beacon-interest%'
union
SELECT
EMAIL,
,DATEADD(s,property_beacon_interest / 1000, '19700101')
FROM hubspot.contact) both
group by email;

# 80

select
  intlist.email
  ,min(intlist.expressed_interest_at) as expressed_interest_at
 from
 (

  (
  select
    hubs.email as email
    ,to_timestamp(hubs.property_beacon_interest/1000) at time zone 'UTC' as expressed_interest_at -- Convert Unix timestamp bigint to timestamp format, display in UTC
   from hubspot.contact hubs
   where hubs.property_beacon_interest is not null
  )

  union all

  (
  select hsc.email
    ,hsc.created_at at time zone 'UTC' as expressed_interest_at -- display in UTC
   from helpscout.conversation hsc
    inner join
      (
      select conversation_id, tag
       from helpscout.conversation_tag
       where lower(trim(tag))='beacon-interest'
      ) hsct on hsc.id=hsct.conversation_id
  )
 ) intlist
 group by email
;

# 81

Select C.email
    ,FORMAT(DATEADD(second, C.property_beacon_interest / 1000, '1970-01-01'), 'yyyy-MM-dd hh:mm:ss UTC') as interest_datetime
INTO #Beacon_Interest
From hubspot.contact C

Where C.property_beacon_interest is not null

UNION ALL

Select C.email
    ,C.created_at as interest_datetime
From helpscout.conversation C

Left Outer Join
helpscout.conversation_tag CT
ON C.id = CT.conversation_id

Where CT.tag = 'beacon_interest'

## Group by Email to find first experssion of interest.

Select BI.email
,min(BI.interest_datetime) as expressed_interest_at
INTO #Final
From #Beacon_Interest BI

Group By BI.email

## Order the final dataset by expressed_interest_at.

Select F.email
,F.expressed_interest_at
From #Final F

ORDER BY F.expressed_interest_at

Drop Table #beacon_interest
Drop Table #Final

# 82

SELECT
  EMAIL
  ,MIN(EXPRESSED_INTEREST_AT) AS EXPRESSED_INTEREST_AT
FROM (
/* This takes email and converted unix timestamp from Hubspot.Contact*/
SELECT
  EMAIL
  ,FROM_UNIXTIME(PROPERTY_BEACON_INTEREST) AS EXPRESSED_INTEREST_AT
FROM HUBSPOT.CONTACT
WHERE
  PROPERTY_BEACON_INTEREST IS NOT NULL

UNION
/* Union will combined results from query above and query below, and eliminate duplicates*/

/* This takes email and created_at timestamp from HC table, making sure an email address has the required tag from HCT table, joining on ID and tag with the chat interest value */
SELECT
  HC.EMAIL
  ,HC.CREATED_AT AS EXPRESSED_INTEREST_AT
FROM HELPSCOUT.CONVERSATION HC
INNER JOIN HELPSCOUT.CONVERSATION_TAG HCT
  ON HC.ID = HCT.CONVERSATION_ID
  AND HCT.TAG = ‘beacon_interest’
) SUB_QUERY
GROUP BY
  EMAIL

# 83

CREATE TABLE interested (
email varchar(255),
created_at TIMESTAMP()
);
INSERT INTO interested
SELECT * FROM hubspot.contact
WHERE property_beacon_interest IS NOT NULL;
INSERT INTO interested
SELECT c.email, c.created_at
FROM helpscout.conversation AS c, helpscout.conversation_tag AS t
WHERE t.tag = "beacon-interest" AND c. id=conversation_id;

# 84

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

# 85

SELECT email, FROM_UNIXTIME(property_beacon_interest/1000) AS expressed_interest_at
FROM hubspot.contact
WHERE property_beacon_interest IS NOT NULL
UNION
SELECT email, created_at AS expressed_interest_at
FROM helpscout.conversation c
RIGHT JOIN helpscout.conversation_tag ct
ON c.id = ct.conversation_id
WHERE tag = 'beacon-interest'
GROUP BY email
ORDER BY expressed_interest_at
;

# 86

CREATE TABLE output (
email varchar,
expressed_interest_at timestamp with time zone
);

INSERT INTO output (email, expressed_interest_at)
SELECT email, to_timestamp(property_beacon_interest/1000)
FROM hubspot.contact
WHERE property_beacon_interest IS NOT NULL;

INSERT INTO output (email, expressed_interest_at)
SELECT email, created_at
FROM helpscout.conversation, helpscout.conversation_tag
WHERE helpscout.conversation_tag.tag = 'beacon-interest'
AND helpscout.conversation_tag.id = helpscout.conversation.id;

SELECT *
FROM output;

# 87

UPDATE hubspot.contact SET  property_beacon_interest = dateadd(s, property_beacon_interest, '19700101')

Select email, MIN(expressed_interest_at)
from( ( Select email, created_at AS expressed_interest_at from hubspot.contact where property_beacon_interest IS NOT NULL) UNION (Select email, created_at AS expressed_interest_at from helpscout.conversation RIGHT JOIN helpscout.conversation_tag ON id=conversation_id WHERE tag = 'beacon-interest')) AS dummytable
GROUPBY  email;

# 88

SELECT    email,
          min(created_at) as expressed_interest_at
FROM      (
          SELECT         hsc.id as id,
                         hsc.email as email,
                         hsc.created_at as created_at,
                         hsct.tag as tag
          FROM           helpscout.conversation             hsc
          INNER JOIN     helpscout.conversation_tag         hsct
          ON             hsc.id = hsct.conversation_id
          WHERE          hsct.tag = 'beacon-interest'
          )
GROUP BY  email;

# 89

SELECT email, MIN(created_at) AS expressed_interest_at
FROM   (SELECT a.email,
               MIN(created_at) AS created_at
        FROM   helpscout.conversation a
               INNER JOIN helpscout.conversation_tag b
                       ON a.id = b.conversation_id
        WHERE  b.tag = "beacon-interest"
        GROUP  BY a.id
        UNION
        SELECT email, time_created AS created_at
        FROM   hubspot.contact
        WHERE  time_created != 'NULL')
GROUP  BY email
ORDER  BY expressed_interest_at;