This is an SQL version of the Dashboard Report for Posts.
This report provides a daily count of posts created within a specified date range. It is designed to track the activity in regular topics, excluding posts from private messages and other special archetypes.
--[params]
-- date :start_date
-- date :end_date
SELECT
p.created_at::date AS "Day",
COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND p.deleted_at ISNULL
AND t.archetype = 'regular'
AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1
SQL Query Explanation
- Parameters:
- The query accepts two parameters,
:start_date
and:end_date
, which define the date range for the report. Both date parameters accept the date format ofYYYY-MM-DD
.
- The query accepts two parameters,
The SQL query performs the following operations:
- Data Selection:
- It selects the date (
created_at::date
) when each post was created and casts it to a date format to ignore the time component. - It also counts the number of posts (
COUNT(p.id)
) created on each date.
- It selects the date (
- Joins:
- The query joins the
posts
table with thetopics
table using anINNER JOIN
. This join ensures that only posts associated with existing topics are considered. - It filters out any topics that have been deleted (
t.deleted_at ISNULL
).
- The query joins the
- Filters:
- It filters posts to include only those within the specified date range (
p.created_at::date BETWEEN :start_date AND :end_date
). - It excludes deleted posts (
p.deleted_at ISNULL
). - It restricts the results to posts from regular topics (
t.archetype = 'regular'
). - It considers only
p.post_type = 1
posts, excluding moderator actions, whispers, and small_action posts.
- It filters posts to include only those within the specified date range (
- Grouping and Ordering:
- The results are grouped by the date of post creation (
GROUP BY p.created_at::date
). - The final output is ordered by the date in ascending order (
ORDER BY 1
), where1
refers to the first column in theSELECT
statement, which is the date.
- The results are grouped by the date of post creation (
Example Results
Day | Count |
---|---|
2023-11-12 | 25 |
2023-11-13 | 35 |
2023-11-14 | 38 |
2023-11-15 | 47 |
2023-11-16 | 36 |
2023-11-17 | 79 |
… | … |