Dashboard Report - Post Edits

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Post Edits.

This dashboard report shows posts that were edited over a specific date range, and includes information about the editor, author of the post, and the reason the post was edited. This report is designed to help administrators monitor and understand the editing activity occurring within their forums, particularly focusing on cases where users other than the original authors make edits to posts.

-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-16

SELECT
    pr.created_at::date as day,
    pr.post_id as post_id,
    pr.user_id AS editor_user_id,
    p.user_id AS author_user_id,
    p.edit_reason
FROM post_revisions pr
JOIN posts p ON p.id = pr.post_id
WHERE pr.created_at BETWEEN :start_date AND :end_date
AND pr.user_id > 0
AND pr.user_id <> p.user_id
ORDER BY pr.created_at ASC

SQL Query Explanation

  • Parameter Declaration:
    • 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.
  • Data Selection: The SELECT statement specifies the columns that will appear in the final report. These include:
    • pr.created_at::date as day: The date a post revision was created, and it’s cast to a date type to remove the time part.
    • pr.post_id: The ID of the post that was edited.
    • pr.user_id AS editor_user_id: The ID of the user who made the edit. This is aliased as editor_user_id to differentiate it from the author of the original post.
    • p.user_id AS author_user_id: The ID of the user who authored the original post, aliased as author_user_id.
    • p.edit_reason: The reason provided for the edit, if any was given.
  • Joining Tables: The FROM clause includes a JOIN statement that combines the post_revisions table (pr) with the posts table (p). The join is done on the condition that post_revisions.post_id matches posts.id, thus linking a revision to the original post details.
  • Filters: The WHERE clause applies several filters to the data:
    • pr.created_at BETWEEN :start_date AND :end_date: Only includes post revisions created within the date range specified by the parameters.
    • pr.user_id > 0: Ensures that the editor is an actual user and not a system operation (a user_id of 0 usually indicates a system operation).
    • pr.user_id <> p.user_id: Filters out revisions made by the post’s original author, focusing only on edits made by different users.
  • Ordering Results: The ORDER BY pr.created_at ASC clause orders the results from oldest to newest based on the timestamp of when each post revision was made.

Example Results

day post editor_user author_user edit_reason
2023-12-20 post_abc user1 user2 Example Edit Reason
2023-12-21 post_dfg user3 user4 Example Edit Reason
2023-12-24 post_xyz user5 user6 NULL
1 Like