Dashboard Report - Posts

Data & reporting
, ,
3

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