SpecSizer Query Research

SELECT 
SubscriberKey, Consent_Level_Summary__c, Cat_Campaign_Most_Recent__c , CreatedDate, PrivacyConsentStatus, HasOptedOutOfEmail, AMC_Status__c, AMC_Last_Activity_Record_ID__c


FROM (

SELECT 
DISTINCT c.Id AS SubscriberKey, c.Consent_Level_Summary__c, i.Cat_Campaign_Most_Recent__c, i.CreatedDate, cpc.PrivacyConsentStatus, c.HasOptedOutOfEmail, c.AMC_Status__c, c.AMC_Last_Activity_Record_ID__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
JOIN ep_w200041_specsizernurture_new_registrants sent ON c.Email = sent.EmailAddress
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
LEFT JOIN ent.[Hard Bounces - Exclusion] hb ON c.ID=hb.subscriberkey

WHERE 
hb.SubscriberKey is null 
AND sent.EmailAddress is not null
AND
(i.Contact_Source_Details_Most_Recent__c = 'Electric Power/specsizer.cat.com/Form Repost' AND
i.Cat_Campaign_Most_Recent__c = 'SpecSizer New Registrant')
    AND Email__c IS NOT NULL
    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 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.CreatedDate >= '01-01-2024' AND i.CreatedDate <= '08-31-2024')
        )t2

WHERE RowNum = 1