title: Calculating last Campaign response for each sales opportunity
body: <p>I have the following tables: Opportunity, Account, Contact, CampaignMember and Campaign. </p>
  
  <p><a href="https://i.stack.imgur.com/fAe2M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fAe2M.png" alt="erd"></a></p>
  
  <p>For each Opportunity, I want to assemble a table using SQL to figure out: <strong>what was the last Campaign response prior to the Opportunity being created?</strong> We call this the "Last Touch" Campaign.</p>
  
  <p>To figure this out, we must start with an Opportunity, look at the related Account, look at all of the Contacts related to this Account and return the CampaignMember for this Account with the <strong>most recent</strong> ResponseDate that is <strong>before</strong> the Opportunity.CreatedDate. </p>
  
  <p>The output should have 3 columns: Opportunity ID, Last Touch Campaign Id, and Last Touch Campaign Type.</p>
  
  <p>Example:
  <a href="https://i.stack.imgur.com/0hjOY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0hjOY.png" alt="erd-example"></a></p>
  
  <p>For Opportunity ID 1, CampaignMember ID 3 has the most recent Response Date that is also <em>before</em> the Opportunity CreatedDate. </p>
  
  <p>For Opportunity ID 2, CampaignMember ID 4 has the most recent Response Date that is before the Opportunity Created Date. We must disregard the Campaign Member responses that occurred after the Opportunity was created. </p>
  
  <p>In our example, it just so happens that the same Campaign was the "Last Touch" prior to an Opportunity being created. </p>