SELECT c.ID,
c.AccountID,
c.Email,
c.FirstName,
a.ID AS AccountID,
a.Name AS AccountName,
p.Related_Agency__c,
e.Tier__c,
t.Name AS TierName,
t.Status__c AS TierStatus,
e.Contacts_at_Location__c,
e.Reward_Amount__c,
FROM [Contact_Salesforce] c
    LEFT JOIN [Engagement__c_Salesforce] e ON c.ID = e.Contact__c
    LEFT JOIN [Account_Salesforce] a ON c.AccountID = a.ID
    LEFT JOIN [Tier__c_Salesforce] t ON e.Tier__c = t.ID
    LEFT JOIN [Program__c_Salesforce] p ON a.ID = p.Related_Agency__c
WHERE c.Focus__c LIKE '%PL%' AND c.Status__c = 'Active'

ID, ..., Email, ..., Reward_Amount__c
123, ..., abc@gmail.com, ..., 123
232, ..., abc@gmail.com, ..., 343
454, ..., def@gmail.com, ..., 234

232, ..., abc@gmail.com, ..., 343
454, ..., abc@gmail.com, ..., 234