TEST_NEW_TA_AUDIENCE

SELECT
EmailAddress, SubscriberKey, Consent_Level_Summary__c,
Business_Unit__c, Region, Cat_Campaign_Most_Recent__c , Mailing_Country__c, LastModifiedDate, System_Language__c, CreatedDate,
FirstName
 
FROM (
SELECT
DISTINCT LOWER(Email__c) AS EmailAddress, i.Region__c AS Region, c.Id AS SubscriberKey, c.Consent_Level_Summary__c, i.Business_Unit__c,i.Cat_Campaign_Most_Recent__c , i.Mailing_Country__c, i.LastModifiedDate, i.System_Language__c, i.CreatedDate,
c.FirstName,
 
ROW_NUMBER() OVER(PARTITION BY c.Id ORDER BY i.LastModifiedDate DESC) as RowNum
 
FROM ent.Interaction__c_Salesforce i
JOIN ent.Contact_Salesforce_1 c ON LOWER(c.Email) = LOWER(i.Email__c)
JOIN TEST_ps_an_en_us_s190010_Retail_TA_Segment_sendable_NEW new ON LOWER(new.EmailAddress) = LOWER(i.Email__c)
INNER JOIN ent.ContactPointConsent_Salesforce AS cpc ON c.Id = cpc.Contact__c
INNER JOIN ent.DataUsePurpose_Salesforce AS dup ON cpc.DataUsePurposeId = dup.Id

WHERE
     Email__c IS NOT NULL
    AND i.Business_Unit__c IN  ('CISD','GASD','Product Support','PS')
    AND Email__c NOT LIKE '%@cat.com'
    AND i.Mailing_Country__c IS NOT NULL
    AND cpc.CaptureContactPointType = 'Email'
    AND cpc.MATM_Owner__c = 'Caterpillar'
    AND dup.Name = 'Caterpillar Marketing'
    AND cpc.PrivacyConsentStatus = 'OptIn' 
    AND (cpc.EffectiveTo IS NULL OR cpc.EffectiveTo < GetDate())
 
    AND ((c.AMC_Last_Activity_Record_ID__c IS NULL) OR (c.AMC_Last_Activity_Record_ID__c <> 'Not Marketable'))
    AND (i.Mailing_State_Province__c != 'QC' OR (i.Mailing_Country__c != 'CA' AND i.Mailing_State_Province__c IS NULL))
    AND  (i.System_Language__c like 'en_%' OR (i.Mailing_Country__c != 'CA' AND i.System_Language__c is null))
    AND c.AMC_Status__c = 'Active'
    AND i.Cat_Campaign_Most_Recent__c NOT IN (
'PCC eNews',
'OHTE Offers',
'Mid America Trucking Show',
'Product Support On-Highway Legacy Engines Initiate Dealer',
'Million Miler',
'Iron Planet Auction - On Highway Legacy Machine',
'Product_Support_ECommerce_Contact',
'Iron Planet Auction - On Highway Legacy Engine',
'EP Spec Sizer Nurture',
'Parts.cat.com Account Registration',
'EP General Nurture',
'Drivecat.com - Mini Survey',
'OHTE Independent Shop Sweepstakes',
'Product Support Million Miler',
'OHTE Trucking Is Life eNews'
)

        )t2
 
WHERE RowNum = 1