This is an SQL version of the Dashboard Report for Topics.
This report provides a count of new topics created on a daily basis within a specified date range. The report does not include personal messages, only regular topics that are visible on the forum.
--[params]
-- date :start_date
-- date :end_date
SELECT
p.created_at::date as day,
COUNT(p.id) AS topics_created
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.post_number = 1
AND p.user_id > 0
AND p.deleted_at ISNULL
AND t.archetype = 'regular'
GROUP BY p.created_at::date
ORDER BY p.created_at::date ASC
SQL Query Explanation
The SQL query performs the following actions:
- Parameter Definition: It accepts two parameters,
:start_date
and:end_date
, which define the date range for the report. Both date parameters accept the format ofYYYY-MM-DD
. - Data Selection: The query selects the creation date of posts (
p.created_at
) and the count of post IDs (COUNT(p.id)
) astopics_created
. - Joins: It joins the
posts
table with thetopics
table on their respectiveid
fields, ensuring that only non-deleted topics (t.deleted_at ISNULL
) are considered. - Filters: The query filters posts to include only those that:
- Were created within the specified date range (
p.created_at::date BETWEEN :start_date AND :end_date
). - Are the first post in a topic (
p.post_number = 1
), indicating the start of a new topic. - Were created by registered users (
p.user_id > 0
), excluding system posts. - Have not been deleted (
p.deleted_at ISNULL
). - Belong to topics of the ‘regular’ archetype (
t.archetype = 'regular'
), excluding personal messages.
- Were created within the specified date range (
- Grouping: The results are grouped by the creation date of the posts (
p.created_at::date
). - Ordering: The final output is ordered by the creation date in ascending order (
ORDER BY p.created_at::date ASC
), providing a chronological view of new topic creation.
Example Results
day | topics_created |
---|---|
2023-11-11 | 18 |
2023-11-12 | 10 |
2023-11-13 | 21 |
2023-11-14 | 19 |
2023-11-15 | 22 |
… | … |