# Email statistics

**URL:** https://meta.discourse.org/t/email-statistics/52478
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [11월 4, 2016, 8:26오전 UTC](https://meta.discourse.org/t/email-statistics/52478 "2016-11-04T08:26:12Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![meglio](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/meglio/32/71444_2.png) [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [11월 4, 2016, 8:26오전 UTC](https://meta.discourse.org/t/email-statistics/52478/1 "2016-11-04T08:26:12Z")

</div>

I run into 100k / mo emails limit of a free SparkPost account.

To better understand what type of email exactly I have to tackle, I had to write an SQL query which calculates email statistics by email type. Sharing it with the community.

Example result:

 ![](https://global.discourse-cdn.com/meta/original/3X/0/a/0a8b48c75668c16df3b539c084b1ebbab260a718.png)

# SQL query

```sql
-- [params]
-- string :date_interval = 1 month

WITH stats AS (
  SELECT
    email_type,
    COUNT(id) as num
  FROM email_logs
  WHERE created_at >= CURRENT_TIMESTAMP - interval :date_interval
  GROUP BY email_type
)

SELECT
  email_type as "Email Type",
  num as "Total Emails",
  ROUND(num::numeric * 100 / (SUM(num) OVER ()), 1) || '%' as "Per Cent",
  ( num / (CURRENT_TIMESTAMP::date
     - (CURRENT_TIMESTAMP - interval :date_interval)::date)
  ) as "Emails per Day"
FROM stats
ORDER BY num DESC

```

---

<div class="post-metadata">

### Author: ![SidV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sidv/32/119460_2.png) [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [11월 5, 2016, 7:00오후 UTC](https://meta.discourse.org/t/email-statistics/52478/2 "2016-11-05T19:00:55Z")

</div>

Wow, really good @meglio, thank you very much! 👍