calculate_salesforce_account_coverage.sql

WITH t1 AS(
 SELECT
   id AS account_id,
   region__c AS region
 FROM
   account
 WHERE
   target_account__c = 1
),
t2 AS(
 SELECT
   accountid AS account_id,
   COUNT(id) AS num_contacts
 FROM
   contact
 GROUP BY
   1
),
t3 AS(
 SELECT
   t1.account_id,
   t1.region, --additional
   t2.num_contacts
 FROM
   t1 LEFT
 JOIN
   t2
   ON t1.account_id = t2.account_id
) SELECT
 region, -- some additional fields
--  (num_contacts IS NOT NULL) AS has_contact,
--  COUNT(1) AS num_accounts,
 SUM(CASE WHEN num_contacts IS NULL THEN 0 ELSE 1 END)*1.0/COUNT(1) AS coverage -- this is the ratio
FROM
 t3
GROUP BY 1