This is an SQL version of the Dashboard Report for Emails Sent.
This report provides a daily breakdown of the total number of emails sent from the Discourse platform within a specified date range. It is useful for monitoring email activity, identifying trends, and ensuring that the email system is functioning correctly.
-- [params]
-- date :start_date
-- date :end_date
SELECT
DATE(created_at) AS date_sent,
COUNT(*) AS total_emails_sent
FROM email_logs
WHERE created_at BETWEEN :start_date AND :end_date
GROUP BY date_sent
ORDER BY date_sent ASC
SQL Query Explanation
The SQL query is designed to retrieve the count of emails sent each day within a given period. Here’s a step-by-step explanation of the query:
Parameters
The query accepts two parameters, :start_date
and :end_date
, allowing the user to specify the date range for the report. Both date parameters accept the date format of YYYY-MM-DD
.
Query Breakdown
DATE(sent_at) AS date_sent
: Extracts the date part from thesent_at
timestamp and labels the resulting column asdate_sent
.COUNT(*) AS total_emails_sent
: Counts the total number of rows (emails) for each group, labeling the result astotal_emails_sent
.FROM email_logs
: This clause indicates that the data will be retrieved from theemail_logs
table, which records each instance of an email being sent.WHERE sent_at BETWEEN :start_date AND :end_date
: This clause filters the data to include only the records where thesent_at
timestamp falls within the specified date range.GROUP BY date_sent
: This clause groups the results by the date on which the emails were sent. This is necessary for theCOUNT
function to calculate the total per day.ORDER BY date_sent ASC
: This clause orders the results in ascending order by thedate_sent
column, ensuring the report shows the data starting from the earliest date to the latest within the range.
Example Results
date_sent | total_emails_sent |
---|---|
2023-11-19 | 264 |
2023-11-20 | 932 |
2023-11-21 | 678 |
2023-11-22 | 637 |
2023-11-23 | 369 |
… | … |