Form Submission Analysis

WITH form_submissions
AS (
  SELECT s."conversion-id" submission_id,
    TIMESTAMP 'epoch' + s.TIMESTAMP / 1000 * INTERVAL '1 second' submission_timestamp,
    row_number() OVER (
      PARTITION BY s.hubspot_contacts_id ORDER BY s.TIMESTAMP
      ) submission_order,
    s."page-id" page_id,
    s."page-url" page_url,
    s."page-title" page_title,
    s.title form_title,
    s."form-id" form_id,
    s.hubspot_contacts_id
  FROM "hubspot_contacts_form-submissions" s
  )
SELECT f.guid form_id,
  f.name form_current_name,
  fs.page_url,
  fs.page_title,
  fs.form_title,
  count(DISTINCT fs.submission_id) total_submissions,
  count(DISTINCT fs.hubspot_contacts_id) total_contacts,
  count(DISTINCT CASE WHEN submission_order = 1 THEN fs.submission_id END) first_submissions,
  round(total_submissions::FLOAT / total_contacts, 2) submission_per_contact
FROM hubspot_forms f
JOIN form_submissions fs ON fs.form_id = f.guid
WHERE submission_timestamp > CURRENT_DATE - 28
GROUP BY 1,2,3,4,5;