SELECT
EmailAddress, Industry, SubscriberKey, Consent_Level_Summary__c,
Business_Unit__c,Cat_Campaign_Most_Recent__c , Mailing_Country__c, LastModifiedDate, Language__c, CreatedDate,
FirstName, LastName, Engagement_Status__c, Last_Engagement_Type__c, Company_Name__c, Job_Role__c, Region



FROM (
SELECT
DISTINCT LOWER(Email__c) AS EmailAddress, i.Industry_Level_2_Master__c AS Industry, i.Industry__c,
c.Id AS SubscriberKey, c.Consent_Level_Summary__c, i.Region__c AS Region,i.Business_Unit__c,i.Cat_Campaign_Most_Recent__c , i.Mailing_Country__c, i.LastModifiedDate, Language__c, i.CreatedDate,
c.FirstName, c.LastName, c.Engagement_Status__c, c.Last_Engagement_Type__c, i.Company_Name__c, i.Job_Role__c,


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 c.Email = 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
(Business_Unit__c LIKE 'Mining' OR i.Industry__c LIKE 'Mining')
AND Email__c IS NOT NULL
AND Email__c NOT LIKE '%@cat.com'
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 (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.Engagement_Status__c = 'Active'
AND i.Mailing_Country__c IS NOT NULL
AND NOT EXISTS
(
SELECT Domain FROM [Mining_Bounce_Domain_Names]
WHERE LOWER(Domain) = LOWER(RIGHT(i.Email__c, LEN(i.Email__c) - CHARINDEX('@', i.Email__c)))
)

)t2


WHERE RowNum = 1