Content Comparison

/* Contact to Contact Dupes - Not part of the same Household Account, Last Names do not match*/
 
    with contact_ph as (
        select
            c.id "Contact SF ID",
            c.accountid "AccountID",
            c.name "Name",
            c.lastname "Lastname",
            c.mobilephone "Contact Phone",
            c.aqb__phonepreference__c "Phone Preference",
            a.aqb__accounttype__c "Account Type",
            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
            left join recordtype r on r.id = a.recordtypeid
        where
            c.mobilephone is not null and c.aqb__type__c <> 'Student' and r.name = 'Household'
    ),
    contact_dupe_ph as (
        select
            c.id "Contact SF ID:Dupe",
            c.accountid "AccountID:Dupe",
            c.name "Name:Dupe",
            c.lastname "Lastname:Dupe",
            c.mobilephone "Contact Phone:Dupe",
            c.aqb__phonepreference__c "Phone Preference:Dupe",
            a.aqb__accounttype__c "Account Type: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"
        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
            left join recordtype r on r.id = a.recordtypeid
        where
            c.mobilephone is not null and c.aqb__type__c <> 'Student' and r.name = 'Household'
    ),
    Dupes as (
        select
            *
        from
            (
                select
                    *
                from
                    contact_ph cp
            ) dd
            join contact_dupe_ph cdp on "Contact SF ID" <> "Contact SF ID:Dupe"
            and "Formatted Phone" = "Formatted Phone:Dupe"
            and "AccountID" <> "AccountID:Dupe"
            and "Lastname" <> "Lastname:Dupe"
        order by
            2,
            3,
            1
    )
select
    *
from
    dupes
order by
    1;