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

part_3_total_revenue_by_touch.sql

/*
Include Parts 1 and 2
*/
    ae_oppy_closed_won_activity_values AS
  ( SELECT ae_oppys_won_visits.oppy_id AS oppy_id, visit_type, visit_details, COUNT, amount,
                                                                                     round(amount/COUNT,2) AS mrr_per_visit
   FROM ae_oppys_won_visits
   INNER JOIN
     ( SELECT COUNT(*) AS COUNT,
                   oppy_id
      FROM ae_oppys_won_visits
      GROUP BY oppy_id ) counts ON counts.oppy_id = ae_oppys_won_visits.oppy_id ),
    total_mrr_per_activity AS
  ( SELECT visit_type,
           visit_details,
           sum(mrr_per_visit) AS total_mrr
   FROM ae_oppy_closed_won_activity_values
   GROUP BY visit_type,
            visit_details )
            
SELECT * 
FROM total_mrr_per_activity
Raw