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:
SQL query
-- [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
