Dashboard Report - Topics

data & reporting
, ,
1

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 of YYYY-MM-DD.
  • Data Selection: The query selects the creation date of posts (p.created_at) and the count of post IDs (COUNT(p.id)) as topics_created.
  • Joins: It joins the posts table with the topics table on their respective id 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.
  • 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
… …
4 Likes