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 of YYYY-MM-DD. 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. Joins: The query joins the posts table with the topics table using an INNER 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). 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. 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), where 1 refers to the first column in the SELECT statement, which is the date. 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 … …

This is great. Would there be a way to be able to define the category/subcategory at run time as the date? And bonus, can we either list the results by user, or define the user as well? What I’m trying to do is see how many posts are made during (range) in my support tickets areas by my support staff.

[תמונה] tknospdr: Would there be a way to be able to define the category/subcategory at run time as the date? And bonus, can we either list the results by user, or define the user as well? Yes, you can use the following query for this: --[params] -- date :start_date -- date :end_date -- null category_id :category_id -- null user_id :user_id -- boolean :include_subcategories = false SELECT u.username AS "User", p.created_at::date AS "Date", COUNT(p.id) AS "Count" FROM posts p INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL INNER JOIN users u ON p.user_id = u.id LEFT JOIN categories c ON t.category_id = c.id WHERE p.created_at::date BETWEEN :start_date AND :end_date AND p.deleted_at IS NULL AND t.archetype = 'regular' AND p.post_type = 1 AND ( :category_id IS NULL OR t.category_id = :category_id OR (:include_subcategories AND c.parent_category_id = :category_id) ) AND (:user_id IS NULL OR p.user_id = :user_id) GROUP BY u.username, p.created_at::date ORDER BY p.created_at::date ASC, u.username Parameters: :start_date & :end_date: Define the reporting timeframe (required) :category_id: Optional filter for a specific category :user_id: Optional filter for a specific user :include_subcategories: Option to include subcategories of the chosen category This query shows: User: Username of the post author Date: The calendar date when posts were created Count: Number of posts created by that user on that date Example Data: User Date Count user 1 2023-01-01 3 user 2 2023-01-01 2 user 3 2023-01-01 1 user 1 2023-01-02 2 user 2 2023-01-02 3 user 1 2023-01-03 1 … … …

דוח דשבורד - פוסטים

קהילה נתונים ודיווח

tknospdr (David Muszynski) 9 בספטמבר,‏ 2025,‏ 8:03pm 4

תודה, זה עוזר מאוד!

נושא		תגובות	צפיות
Aggregate dashboard report data by time interval Data & reporting sql-query	4	1254	24 בספטמבר,‏ 2022
Dashboard Report - Topics Data & reporting sql-query , dashboard-reports , dashboard-sql	0	478	12 בדצמבר,‏ 2023
Content Audit : Report to pull Categories, Topics and Replies Data & reporting sql-query	2	321	31 בינואר,‏ 2025
Top X posters in a given timeframe Data & reporting sql-query	3	297	28 במאי,‏ 2024
Posts created for period Data & reporting sql-query	0	234	14 בספטמבר,‏ 2016

דוח דשבורד - פוסטים

נושאים קשורים