Part 2 of Lead Engagement Scoring Query: https://gist.github.com/henryhund/dc9b2d47c00f018c098c#file-lead_scoring_complete_query-sql

part_2_all_touches_for_closed_wons.sql

/*
Include Part 1
*/
   ae_oppys_won AS
  ( SELECT DISTINCT oppy_id,
                    oppy_date,
                    is_closed,
                    is_won,
                    account_id,
                    oppy_amount
   FROM
     ( SELECT DISTINCT oppy.stagename AS oppy_stage,
                       oppy.id AS oppy_id,
                       oppy.createddate AS oppy_date,
                       oppy.isclosed AS is_closed,
                       oppy.iswon AS is_won,
                       account.id AS account_id,
                       oppy.amount::float AS oppy_amount
      FROM salesforce.account account
      INNER JOIN salesforce.opportunity oppy ON account.id = oppy.accountid
      WHERE is_closed = 1
        AND is_won = 1
        AND oppy.amount > 0 ) ),
    ae_oppys_won_visits AS
  ( SELECT DISTINCT prospects.id AS prospect_id,
                    account_id,
                    activity.created_at AS visit_date,
                    visit_details AS visit_details,
                    activity.visit_type AS visit_type,
                    oppy_amount AS amount,
                    oppy_id
   FROM ae_oppys_won
   INNER JOIN salesforce.contact contacts ON ae_oppys_won.account_id = contacts.accountid
   INNER JOIN pardot.prospect prospects ON contacts.id = prospects.crm_contact_fid
   INNER JOIN all_prospects_visits activity ON prospects.id = activity.prospect_id
   WHERE -- make sure the visit happend during the 3 months leading up to the oppy
 visit_date > dateadd(MONTH,-3, oppy_date)
     AND visit_date < oppy_date )
     
SELECT * 
FROM ae_oppys_won_visits