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

part_1_all_touches.sql

WITH 
    all_prospects_visits AS
  ( SELECT DISTINCT prospects.id AS prospect_id,
                    activity.created_at AS visit_date,
                    details AS visit_details,
                    activity.type AS visit_type,
                    activity.created_at created_at
   FROM pardot.prospect prospects
   INNER JOIN pardot.visitoractivity activity ON prospects.id = activity.prospect_id
   WHERE /*
                Limit to views, form submissions, and video (>=75% watched) i.e.,
                activity types 2, 4 and 34.
                See http://developer.pardot.com/kb/api-version-3/object-field-references/#visitor-activity-types
                for a list of all activity types
            */ activity.type IN (2,
                                 34,
                                 4) -- exclude form views

     AND (activity.type_name NOT IN ('Form')
          OR activity.type IN (4,
                               34))
     AND visit_details IS NOT NULL ),
    count_activities AS
  ( SELECT visit_details,
           visit_type,
           count(*) AS total_count
   FROM all_prospects_visits
   GROUP BY visit_details,
            visit_type )
            
SELECT * 
FROM count_activities