SQL to Self Join

-- Mail Solicitation;

-- create features;

drop table ANALYTICS_STG..CAMPAIGN_ADDRESS_3;

-- 2,038,831,646 rows affected

create table ANALYTICS_STG..CAMPAIGN_ADDRESS_3 as

select a.*,

row_number() over(partition by a.EMAIL_ADDRESS_ID order by a.EMAIL_ADDRESS_ID, a.SEND_TS) seq_freq,

extract(DAY from a.SEND_TS - LAG(a.SEND_TS, 1) OVER (partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEND_TS)) as recency,

b.reach_freq as tot_freq

from ANALYTICS_STG..CAMPAIGN_ADDRESS_2 a

join (select EMAIL_ADDRESS_ID, count(*) as reach_freq from ANALYTICS_STG..CAMPAIGN_ADDRESS_2 group by 1) b

on a.EMAIL_ADDRESS_ID = b.EMAIL_ADDRESS_ID;

-- Mail Response;

-- Dedupe response;

create table ANALYTICS_STG..RESPONSE_ADDRESS_2 as

select a.*,

case when upper(a.FEEDBACK_EVENT_CD)='OPEN' then 1 else 0 end FEEDBACK_EVENT_CD_1,

case when upper(a.FEEDBACK_EVENT_CD)='CLICK' then 1 else 0 end FEEDBACK_EVENT_CD_2,

case when upper(a.FEEDBACK_EVENT_CD)='UNDEL' then 1 else 0 end FEEDBACK_EVENT_CD_3,

case when upper(a.FEEDBACK_EVENT_CD)='CONVERSION' then 1 else 0 end FEEDBACK_EVENT_CD_4,

case when upper(a.FEEDBACK_EVENT_CD)='UNSUB' then 1 else 0 end FEEDBACK_EVENT_CD_5,

case when upper(a.FEEDBACK_EVENT_CD)='SPAM' then 1 else 0 end FEEDBACK_EVENT_CD_6

from (

select *, row_number()

over (partition by CAMPAIGN_ID, ELECTRONIC_ADDRESS_ID, CONCEPT_ID, FEEDBACK_EVENT_CD, MAIL_TS

order by  CAMPAIGN_ID, ELECTRONIC_ADDRESS_ID, CONCEPT_ID, FEEDBACK_EVENT_CD, MAIL_TS, FEEDBACK_EVENT_TS

) row_id

from ANALYTICS_STG..RESPONSE_ADDRESS_1

) a

where a.row_id = 1;

-- create response features

create table ANALYTICS_STG..RESPONSE_ADDRESS_3 as

select a.*,

row_number() over (partition by a.ELECTRONIC_ADDRESS_ID order by a.ELECTRONIC_ADDRESS_ID, a.MAIL_TS, a.FEEDBACK_EVENT_TS) seq_engagement,

b.tot_engagement,

b.tot_open,

b.tot_click,

b.tot_unsub,

extract(DAY from (a.FEEDBACK_EVENT_TS - a.MAIL_TS)) as feedback_day

from ANALYTICS_STG..RESPONSE_ADDRESS_2 a

inner join (select ELECTRONIC_ADDRESS_ID,

count(*) as tot_engagement,

sum(FEEDBACK_EVENT_CD_1) as tot_open,

sum(FEEDBACK_EVENT_CD_2) as tot_click,

sum(FEEDBACK_EVENT_CD_5) as tot_unsub

from ANALYTICS_STG..RESPONSE_ADDRESS_2

where FEEDBACK_EVENT_CD_1 = 1 or FEEDBACK_EVENT_CD_2 = 1 or FEEDBACK_EVENT_CD_5 = 1

group by 1

) b

on a.ELECTRONIC_ADDRESS_ID = b.ELECTRONIC_ADDRESS_ID

where a.FEEDBACK_EVENT_CD_1 = 1 or a.FEEDBACK_EVENT_CD_2 = 1 or a.FEEDBACK_EVENT_CD_5 = 1;

-- Join solicitations and responses

create table ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_1 as

select a.*,

b.FEEDBACK_EVENT_TS, 

b.FEEDBACK_EVENT_CD, 

b.FEEDBACK_EVENT_CD_1,

b.FEEDBACK_EVENT_CD_2,

b.FEEDBACK_EVENT_CD_5,

b.SEQ_ENGAGEMENT,

b.TOT_CLICK,

b.TOT_OPEN,

b.TOT_ENGAGEMENT,

b.TOT_UNSUB,

b.FEEDBACK_DAY

from  ANALYTICS_STG..CAMPAIGN_ADDRESS_3 a

inner join ANALYTICS_STG..RESPONSE_ADDRESS_3 b

on a.CAMPAIGN_ID = b.CAMPAIGN_ID

and a.CONCEPT_ID = b.CONCEPT_ID

and a.EMAIL_ADDRESS_ID = b.ELECTRONIC_ADDRESS_ID

and a.SEND_TS = b.MAIL_TS

;

-- Last attributions;

create table ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_2 as

select a.*,

b.FEEDBACK_EVENT_TS, 

b.FEEDBACK_EVENT_CD, 

b.FEEDBACK_EVENT_CD_1, 

b.FEEDBACK_EVENT_CD_2, 

b.FEEDBACK_EVENT_CD_5, 

b.TOT_ENGAGEMENT, 

b.SEQ_ENGAGEMENT, 

b.TOT_CLICK, 

b.TOT_OPEN, 

b.TOT_UNSUB,

b.FEEDBACK_DAY,

SUM(case when b.FEEDBACK_EVENT_CD_1=1 then 1 else 0 end) OVER(partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEQ_FREQ, b.SEQ_ENGAGEMENT rows unbounded preceding)/a.SEQ_FREQ CUM_OPEN_PERC,

SUM(case when b.FEEDBACK_EVENT_CD_2=1 then 1 else 0 end) OVER(partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEQ_FREQ, b.SEQ_ENGAGEMENT rows unbounded preceding)/a.SEQ_FREQ CUM_CLICK_PERC,

SUM(case when b.FEEDBACK_EVENT_CD_5=1 then 1 else 0 end) OVER(partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEQ_FREQ, b.SEQ_ENGAGEMENT rows unbounded preceding)/a.SEQ_FREQ CUM_UNSUB_PERC

from ANALYTICS_STG..CAMPAIGN_ADDRESS_3 a

left join ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_1 b

on a.CAMPAIGN_ID=b.CAMPAIGN_ID

and a.CONCEPT_ID=b.CONCEPT_ID

and a.EMAIL_ADDRESS_ID = b.EMAIL_ADDRESS_ID

and a.SEQ_FREQ = b.SEQ_FREQ

;