SELECT
EmailAddress, Industry_Level_2_Master__c, Industry__c, SubscriberKey, Consent_Level_Summary__c,
Business_Unit__c,Cat_Campaign_Most_Recent__c , Mailing_Country__c, LastModifiedDate, Language__c, CreatedDate,
FirstName, LastName
FROM (
SELECT
DISTINCT LOWER(Email__c) AS EmailAddress, Industry_Level_2_Master__c, i.Industry__c,
c.Id AS SubscriberKey, Consent_Level_Summary__c, Business_Unit__c,Cat_Campaign_Most_Recent__c , Mailing_Country__c, i.LastModifiedDate, Language__c, i.CreatedDate,
c.FirstName, c.LastName,
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)
LEFT JOIN ENT.[Inactive Contacts - Exclusion] ia ON c.Id=ia.ContactId
WHERE
((ia.ContactId is null ) AND
(Business_Unit__c LIKE 'Mining' OR i.Industry__c LIKE 'Mining')
AND Email__c IS NOT NULL
AND (
(Consent_Level_Summary__c in ('Express Consent', 'Validated Consent', 'Legacy Consent') AND Mailing_Country__c = 'US')
OR
Consent_Level_Summary__c in ('Express Consent', 'Validated Consent'))
AND Language__c LIKE 'en_%'
AND Email__c NOT LIKE '%@cat.com'
AND MAILING_COUNTRY__C IS NOT NULL)
)t2
WHERE RowNum = 1
Comments