Cumulative unique visitors that have engaged

visitor.sql

SELECT COUNT(*) as ecount 

FROM 
       (SELECT contact_id 

FROM 
       (SELECT w.data as contact_id from chowan2019_dru.webform_submitted_data w 
       INNER JOIN chowan2019_dru.webform_component c ON c.cid = w.cid AND (c.name = 'Contact ID' OR c.name = 'Existing Contact') AND w.nid = c.nid 
       INNER JOIN chowan2019_dru.webform_submissions ws ON ws.nid = w.nid AND w.sid = ws.sid   
       

       WHERE (1)  AND w.nid IN (428,431,551,552,564,571,609,667,670,671)
       AND w.data IS NOT NULL and w.data <> '' AND DATE(FROM_UNIXTIME(ws.completed)) <= '2019-09-17'
       

GROUP BY w.sid
       UNION
       SELECT p.entity_id as download 
       

FROM chowan2019_dru.watchdog_nrm wn 
  LEFT JOIN chowan2019_civi.civicrm_value_nrmpurls_5 p
       ON REPLACE(wn.purl, '.chowan2020.com', '') COLLATE utf8_unicode_ci = p.purl_145
       

WHERE wn.location LIKE '%files/%' AND DATE(FROM_UNIXTIME(timestamp)) <= '2019-09-17'
       ) as e
       INNER JOIN chowan2019_civi.civicrm_value_nrmpurls_5 p ON p.entity_id = e.contact_id
       

WHERE p.purl_145 IN (SELECT REPLACE(purl,'.chowan2020.com','') AS visit 

FROM chowan2019_dru.watchdog_nrm
	     

WHERE DATE(FROM_UNIXTIME(timestamp)) <= '2019-09-17' AND purl <> 'chowan2020.com' AND purl LIKE '%chowan2020.com')
       

GROUP BY contact_id
       ) as ue