Content Comparison

with duplicates as(
with contact_ph as
(
select  c.id "Contact SF ID",
        c.mobilephone "Contact Phone",
        c.aqb__phonepreference__c "Phone Preference",
        
        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"
        from(select cp.*,
                    regexp_replace(lower(trim(cp.mobilephone)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.contact cp
                    )c
        left join salesforce_db.salesforce_sch.user u on u.id = c.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = c.lastmodifiedbyid
        left join salesforce_db.salesforce_sch.account a on a.id = c.accountid
        where c.mobilephone is not null 
        --and c.aqb__type__c <> 'Student'

),


ac_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
        ac.name "Alt Channel Name:Dupe",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Dupe",
        ac.aqb__contact__c "ContactID:Dupe",
        ac.aqb__account__c "AccountID: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 "ContactID: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 c.id = ac.aqb__contact__c
        left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
        where --"ContactID:Dupe" is not null --and ac.aqb__account__c is not null
        r.name = 'Phone'
        --and c.aqb__type__c <> 'Student'
),

Dupes as
( select * from (
       select * from contact_ph cp
            )dd
       join ac_dupes acd on left("Contact SF ID",15) = "Linked To:Dupe" 
       and "Formatted Phone" = "Formatted Phone:Dupe"
       order by 2,3,1
)

select 
       "Contact SF ID",
       "Contact Phone",
       --"Phone Preference",
       "Formatted Phone",

       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("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("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

) select * from duplicates d;