Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/responses.sql Secret

Last active December 25, 2018 18:18
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mattm/a3ef18604b3ade2e5943c0b9cf475849 to your computer and use it in GitHub Desktop.
Save mattm/a3ef18604b3ade2e5943c0b9cf475849 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Screener Question Responses
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment