7.2.1 Alternate Channel Phone De-duplication

/* Alternate Channel De-Duplication - Marketing Automation/Preference Centre

Business Owners: Jessica Wood, Alain Gasquet

Developer Notes:
Records were trimmed and cleaned using regular expression replace
If number starts with +612, +6102, +613, +6103, +614, +6104, +617, +6107, +618, +6108 the numbers were considered Australian and +61 was replaced by 0. 
For each unique formatted phone number per contact, sorted in descending order, dupliacate records were identified if there was an exact match.

*/

--create or replace view sp_advancement_db.tda_sch.altchannel_phone_dedupe_vw as
with aq_contact_duplicates as(
with ac_primary as
(
select ac.id "Alt Channel SF ID:Primary",
        ac.name "Alt Channel Name:Primary",
        ac.aqb__contact__c "ContactID:Primary",
        ac.aqb__account__c "AccountID:Primary",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Primary",
        ac.aqb__status__c "Status:Primary",
        ac.aqb__type__c "Type:Primary",
        ac.aqb__phone__c "Phone:Primary",
        
        case 
        when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
        when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone",  '^(000)', '0')
        when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone",  '^(00)', '0')
        when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
        when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
        when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
        when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
        when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
        when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
        when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
        when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
        when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
        when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
        else "Formatted Phone"
        end "Formatted Phone:Primary",
        ac.createddate "CreatedDate:Primary",
        ac.lastmodifieddate "LastModifiedDate:Primary",
        u.name "CreatedBy:Primary",
        u1.name "LastModifiedBy:Primary",
        ac.aqb__source__c "Source:Primary",
        row_number() over(partition by "Linked To:Primary", "Formatted Phone:Primary" order by "CreatedDate:Primary" desc ) seq
        from(select a.*,
                    regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.aqb__aqaddress__c a
                    )ac
        left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
        left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
        left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = "Linked To:Primary"
        left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
        where 
        r.name = 'Phone' and ("Linked To:Primary"  like '003%')
        order by 3,7,14

),


ac_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
        ac.name "Alt Channel Name:Dupe",
        ac.aqb__contact__c "ContactID:Dupe",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Dupe",
        ac.aqb__status__c "Status:Dupe",
        ac.aqb__type__c "Type:Dupe",
        ac.aqb__phone__c "Phone:Dupe",

        case 
        when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
        when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone",  '^(000)', '0')
        when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone",  '^(00)', '0')
        when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
        when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
        when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
        when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
        when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
        when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
        when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
        when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
        when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
        when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
        else "Formatted Phone"
        end "Formatted Phone:Dupe",
        ac.createddate "CreatedDate:Dupe",
        ac.lastmodifieddate "LastModifiedDate:Dupe",
        u.name "CreatedBy:Dupe",
        u1.name "LastModifiedBy:Dupe",
        ac.aqb__source__c "Source:Dupe",
        ac.aqb__linkedto__c,
        row_number() over(partition by "Linked To:Dupe" order by "CreatedDate:Dupe" desc ) seq
        from(select a.*,
                    regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.aqb__aqaddress__c a
                    )ac
        left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
        left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
        left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = "Linked To:Dupe"
        left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
        where 
        r.name = 'Phone' and ("Linked To:Dupe"  like '003%')
        
),

Dupes as
( select * from (
       select * from ac_primary acp 
       where acp.seq = 1
            )dd
       join ac_dupes acd on "Linked To:Primary" = "Linked To:Dupe" 
       where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
      and "Formatted Phone:Primary" = "Formatted Phone:Dupe"
       order by 2,3,1
)

select "Alt Channel SF ID:Primary",
       "Alt Channel Name:Primary",
       "ContactID:Primary",
       "AccountID:Primary",
       "Phone:Primary",
       "Formatted Phone:Primary",
       "Status:Primary",
       "Type:Primary",
       "LastModifiedDate:Primary",       
       "CreatedDate:Primary",
       "LastModifiedBy:Primary",
       "CreatedBy:Primary",
       "Source:Primary",       
       listagg("Alt Channel SF ID:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Alt Channel ID:Dupes",
       listagg("Alt Channel Name:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Alt Channel Names:Dupes",
       listagg("Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Phones:Dupes",
       listagg("Formatted Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Formatted Phones:Dupes",
       listagg("Status:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Status:Dupes",
       listagg("Type:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Type:Dupes",
       listagg("CreatedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedBy:Dupes",
       listagg("CreatedDate:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedDate:Dupes",
       listagg("LastModifiedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedBy:Dupe",
       listagg("LastModifiedDate:Dupe",',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedDate:Dupe",
       listagg("Source:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Source:Dupes",
       count("Alt Channel SF ID:Dupe") "No of Dupes"
       from dupes d
       group by all
       order by 2,3,1

),


------------------------------------------
/* Account Phone duplicates in Alternate Channel */

aq_account_duplicates as(
with account_primary as
(
select ac.id "Alt Channel SF ID:Primary",
        ac.name "Alt Channel Name:Primary",
        ac.aqb__contact__c "ContactID:Primary",
        ac.aqb__account__c "AccountID:Primary",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Primary",
        ac.aqb__status__c "Status:Primary",
        ac.aqb__type__c "Type:Primary",
        ac.aqb__phone__c "Phone:Primary",
        
        case 
        when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
        when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone",  '^(000)', '0')
        when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone",  '^(00)', '0')
        when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
        when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
        when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
        when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
        when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
        when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
        when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
        when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
        when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
        when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
        else "Formatted Phone"
        end "Formatted Phone:Primary",
        ac.createddate "CreatedDate:Primary",
        ac.lastmodifieddate "LastModifiedDate:Primary",
        u.name "CreatedBy:Primary",
        u1.name "LastModifiedBy:Primary",
        ac.aqb__source__c "Source:Primary",
        ac.aqb__linkedto__c,
        row_number() over(partition by "Linked To:Primary", "Formatted Phone:Primary" order by "CreatedDate:Primary" desc ) seq
        from(select a.*,
                    regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.aqb__aqaddress__c a
                    )ac
        left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
        left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
        --left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = ac.aqb__linkedto__c
        left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Primary"
        where r.name = 'Phone' and ("Linked To:Primary"  like '001%')
        order by 3,7,14

),


account_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
        ac.name "Alt Channel Name:Dupe",
        ac.aqb__contact__c "ContactID:Dupe",
        ac.aqb__account__c "AccountID:Dupe",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Dupe",
        ac.aqb__status__c "Status:Dupe",
        ac.aqb__type__c "Type:Dupe",
        ac.aqb__phone__c "Phone:Dupe",

        case 
        when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
        when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone",  '^(000)', '0')
        when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone",  '^(00)', '0')
        when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
        when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
        when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
        when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
        when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
        when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
        when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
        when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
        when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
        when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
        else "Formatted Phone"
        end "Formatted Phone:Dupe",
        ac.createddate "CreatedDate:Dupe",
        ac.lastmodifieddate "LastModifiedDate:Dupe",
        u.name "CreatedBy:Dupe",
        u1.name "LastModifiedBy:Dupe",
        ac.aqb__source__c "Source:Dupe",
        ac.aqb__linkedto__c,
        row_number() over(partition by "Linked To:Dupe" order by "CreatedDate:Dupe" desc ) seq
        from(select a.*,
                    regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.aqb__aqaddress__c a
                    )ac
        left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
        left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
        --left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = ac.aqb__linkedto__c
        left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Dupe"
        where
        r.name = 'Phone' and ("Linked To:Dupe"  like '001%')
),

Dupes as
( select * from (
       select * from account_primary acp 
       where acp.seq = 1
            )dd
       join account_dupes acd on "Linked To:Primary" = "Linked To:Dupe" 
       where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
       and "Formatted Phone:Primary" = "Formatted Phone:Dupe"
       order by 2,3,1
)

select "Alt Channel SF ID:Primary",
       "Alt Channel Name:Primary",
       "ContactID:Primary",
       "AccountID:Primary",
       "Phone:Primary",
       "Formatted Phone:Primary",
       "Status:Primary",
       "Type:Primary",
       "LastModifiedDate:Primary",       
       "CreatedDate:Primary",
       "LastModifiedBy:Primary",
       "CreatedBy:Primary",
       "Source:Primary",       
       listagg("Alt Channel SF ID:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Alt Channel ID:Dupes",
       listagg("Alt Channel Name:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Alt Channel Names:Dupes",
       listagg("Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Phones:Dupes",
       listagg("Formatted Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Formatted Phones:Dupes",
       listagg("Status:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Status:Dupes",
       listagg("Type:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Type:Dupes",
       listagg("CreatedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedBy:Dupes",
       listagg("CreatedDate:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedDate:Dupes",
       listagg("LastModifiedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedBy:Dupe",
       listagg("LastModifiedDate:Dupe",',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedDate:Dupe",
       listagg("Source:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Source:Dupes",
       count("Alt Channel SF ID:Dupe") "No of Dupes"
       from dupes d
       --where "CreatedBy:Primary" = 'mule_svc'
       group by all
       order by 2,3,1

),


Segment_01 as
(
select '01' "Segment Order", 'Contact Primary Phone created by AQ, Dupe Count = 1' "Segment Name",
acd.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where  "CreatedBy:Primary" not in('mule_svc', 'Student Integration User')
and "CreatedBy:Primary" = 'AQConversion'
and "No of Dupes" = 1
),

Segment_02 as
(select '02' "Segment Order",'Contact Primary Phone NOT created by AQ/mule_svc Dupe Count = 1' "Segment Name",
acd.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where  "No of Dupes" = 1
and "CreatedBy:Primary" not in ('mule_svc', 'AQConversion', 'Student Integration User')
),

Segment_03 as
(
select '03' "Segment Order",'Contact Primary Phone NOT created by mulesoft, Dupe Count > 1' "Segment Name",
acd.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1", 
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where  "No of Dupes" > 1
and "CreatedBy:Primary" not in('mule_svc', 'Student Integration User')
),

Segment_04 as
(
select '04' "Segment Order",'Contact Primary Phone created by mule_svc' "Segment Name",
acd.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where  "CreatedBy:Primary" in('mule_svc', 'Student Integration User')
),

Segment_05 as
(
select '05' "Segment Order", 'Account Primary Phone created by AQ, Dupe Count = 1' "Segment Name",
aad.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_account_duplicates aad
where  "CreatedBy:Primary" not in ('Student Integration User','mule_svc')
and "CreatedBy:Primary" = 'AQConversion'
and "No of Dupes" = 1
),

Segment_06 as
(select '06' "Segment Order",'Account Primary Phone NOT created by AQ/mule_svc Dupe Count = 1' "Segment Name",
aad.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_account_duplicates aad
where  "No of Dupes" = 1
and "CreatedBy:Primary" not in ('mule_svc', 'AQConversion', 'Student Integration User')
),

Segment_07 as
(
select '07' "Segment Order",'Account Primary Phone NOT created by mulesoft, Dupe Count > 1' "Segment Name",
aad.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1", 
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_account_duplicates aad
where  "No of Dupes" > 1
and "CreatedBy:Primary" not in('Student Integration User','mule_svc')
),

Segment_08 as
(
select '08' "Segment Order",'Account Primary Phone created by mule_svc' "Segment Name",
aad.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_account_duplicates aad
where  "CreatedBy:Primary" in('Student Integration User', 'mule_svc')
)

select * from ( select * from Segment_01
                union all 
                select * from Segment_02
                union all 
                select * from Segment_03
                union all
                select * from Segment_04
                union all
                select * from Segment_05
                union all 
                select * from Segment_06
                union all 
                select * from Segment_07
                union all
                select * from Segment_08
                );