0

So I'm relatively new to PostgreSQL, but here we go:

I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays all pertinent information. The query returns everything how I wanted, except for a crazy impossible open rate. I'm not sure how I can best find the unique open rate by email send.

Here is the query I have so far, which returns valid results for everything but open rate:

 SELECT
  MIN(timestamp) as "Date",
  "EmailEvents".send_name as "Send Name",
  cohort as "Cohort",
  product as "Product",
  COUNT(DISTINCT email) AS "Send Size",
  opencount.openRate AS "Open Rate"       
 FROM
  "EmailEvents",
  (SELECT
   send_name,
   (100*(SUM(CASE   
    WHEN email IS NOT NULL   
    AND event = 'open' THEN 1   
    ELSE 0         
    END) / SUM(DISTINCT CASE   
    WHEN email IS NOT NULL THEN 1   
    ELSE 0         
   END))) AS openRate                   
  FROM
   "EmailEvents"                  
  GROUP BY
   send_name) opencount                 
 WHERE
  "EmailEvents".send_name = opencount.send_name                      
  GROUP BY
  "EmailEvents".send_name,
  product,
  cohort,
  opencount.openRate      
 ORDER BY
  MIN(timestamp) DESC

Any help would be appreciated. Thanks!!

3
  • Try casting at least one of your sum() expressions as numeric. (Compare the results of SELECT 1/5 and SELECT 1::numeric/5!) Nov 25, 2014 at 14:44
  • What is the SUM(DISTINCT CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END) supposed to do? Is it to COUNT(DISTINCT email)? Nov 25, 2014 at 15:36
  • Yes I'm not sure why I wrote it like that, but you're correct it should be just COUNT(DISTINCT email)
    – amizzo
    Nov 25, 2014 at 18:39

1 Answer 1

0

Answering my own question...but after playing around with it for awhile this seems to provide the correct answer:

SELECT
  MIN(timestamp) as "Date",
  "EmailEvents".send_name as "Send Name",
  cohort as "Cohort",
  product as "Product",
  COUNT(DISTINCT email) AS "Send Size",
  (100*openCount.distinctCount / COUNT(DISTINCT email))::numeric AS "Open Rate" 
FROM
  "EmailEvents",
  (SELECT
   DISTINCT send_name,
   COUNT(distinct email) as distinctCount 
   FROM
   "EmailEvents" 
WHERE
   event='open' 
GROUP BY
   send_name) openCount 
WHERE
   "EmailEvents".send_name = openCount.send_name 
   AND EXTRACT(month 
   from
   timestamp) = EXTRACT(month 
   from
   now()) 
GROUP BY
  "EmailEvents".send_name,
  product,
  cohort,
  openCount.distinctCount 
ORDER BY
  MIN(timestamp) DESC

Hope this helps someone else looking for a similar solution!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.