Dashboard Report - Posts

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
… …
2 Likes

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.

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
… … …
2 Likes

Thank you, that is a big help!

2 Likes