cisd New

SELECT SubscriberKey, EmailAddress, System_Language__c, Mailing_Country__c, First_Name__c, Cat_Campaign_Most_Recent__c, Business_Unit__c, System_Opt_in_for_eMail__c, DateAdded, PCCReg
FROM (
SELECT
DISTINCT i.SubscriberKey
	,i.EmailAddress
	,i.System_Language__c
	,i.Mailing_Country__c
	,i.First_Name__c
	,i.Cat_Campaign_Most_Recent__c
	,i.Business_Unit__c
	,i.System_Opt_in_for_eMail__c
	,i.DateAdded
	,i.PCCReg,
 
ROW_NUMBER() OVER(PARTITION BY c.ID ORDER BY inta.LastModifiedDate DESC) as RowNum
 
FROM ent.Interaction__c_Salesforce inta
JOIN ent.Contact_Salesforce_1 c ON LOWER(c.Email) = LOWER(inta.Email__c)
JOIN [Proposed_Retail_TA_News_2024_INCLUDE] i ON LOWER(inta.Email__c) = LOWER(i.EmailAddress)
LEFT JOIN ps_an_en_us_s190010_Retail_TA_Segment_sendable_2021 mst ON LOWER(inta.Email__c) = LOWER(mst.EmailAddress)
WHERE 1 = 1
    AND i.SYSTEM_OPT_IN_FOR_EMAIL__C = '1'
    AND NOT EXISTS (
        SELECT NULL
        FROM [Proposed_Retail_TA_News_2024_EXCLUDE] ex
        WHERE 1 = 1
            AND i.SubscriberKey = ex.SubscriberKey
    ))t2
 
WHERE RowNum = 1