Dashboard Report - Flags Status

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Flags Status.

The Flags Status report is designed to provide insights into the moderation process by analyzing the time taken to resolve flagged posts within a specified period. It includes details about the type of flag raised, the post and users involved, the creation date of the flag, the resolution status, the staff member who handled the flag, and the time taken to reach a resolution.

This report is useful for community managers and moderators to assess the efficiency of the moderation team, identify any problems in the resolution process, and ensure that community standards are upheld in a timely manner.

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

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- time to resolution in minutes
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
poster_data AS (
    SELECT pa.id,
           p.user_id AS poster_id,
           p.topic_id,
           p.post_number,
           u.username_lower AS poster_username,
           u.uploaded_avatar_id AS poster_avatar_id
    FROM period_actions pa
    JOIN posts p ON p.id = pa.post_id
    JOIN users u ON u.id = p.user_id
),
flagger_data AS (
    SELECT pa.id,
           u.id AS flagger_id,
           u.username_lower AS flagger_username,
           u.uploaded_avatar_id AS flagger_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = pa.user_id
),
staff_data AS (
    SELECT pa.id,
           u.id AS staff_id,
           u.username_lower AS staff_username,
           u.uploaded_avatar_id AS staff_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
    SELECT pat.id,
           pat.id AS flag_type
    FROM post_action_types pat
)
SELECT
    CASE 
        WHEN pat.flag_type = 3 THEN 'off_topic'
        WHEN pat.flag_type = 4 THEN 'inappropriate'
        WHEN pat.flag_type = 6 THEN 'notify_user'
        WHEN pat.flag_type = 7 THEN 'notify_moderators'
        WHEN pat.flag_type = 8 THEN 'spam'
    END "type",
    pa.post_id as flagged_post_id,
    pd.poster_id as poster_user_id,
    fd.flagger_id as flagger_user_id,
    DATE(pa.created_at) as flag_created,
    CASE 
        WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
        WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
        WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
    END "resolution",
    sd.staff_id as assigned_user_id,
    ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC

SQL Query Explanation

The query is structured using Common Table Expressions (CTEs) that prepare a subset of data that is then used in the final SELECT statement to compile the report.

Parameters

The query accepts two parameters:

  • :start_date: The beginning of the period for which to generate the report.
  • :end_date: The end of the period for which to generate the report.

CTEs

period_actions

This CTE selects post actions (flags) that fall within the specified date range and are of specific types (3, 4, 6, 7, 8), which correspond to different flag reasons. It calculates the ‘time_to_resolution_minutes’ by finding the difference between the creation time of the flag and the time it was responded to (agreed, disagreed, or deferred).

poster_data

This CTE joins the period_actions with the posts and users tables to retrieve information about the poster of the flagged post, including their user ID, topic ID, post number, and username.

flagger_data

This CTE joins the period_actions with the users table to get information about the user who flagged the post, including their user ID and username.

staff_data

This CTE retrieves information about the staff member who handled the flag by joining period_actions with the users table based on the IDs of the staff members who agreed, disagreed, or deferred the flag.

flag_types

This CTE simply selects the IDs from the post_action_types table, which will be used to determine the type of flag in the final SELECT statement.

Final SELECT

The final SELECT statement combines all the CTEs to present a comprehensive report. It includes the following columns:

  • type: The type of flag raised, determined by the flag_type ID.
  • flagged_post_id: The ID of the flagged post.
  • poster_user_id: The ID of the user who posted the flagged content.
  • flagger_user_id: The ID of the user who flagged the content.
  • flag_created: The date when the flag was created.
  • resolution: The resolution status of the flag (agreed, disagreed, deferred).
  • assigned_user_id: The ID of the staff member who handled the flag.
  • resolution_time (minutes): The time taken to resolve the flag, in minutes, rounded to two decimal places.

The report is then ordered by the creation date of the flag (pa.created_at) in ascending order.

Example Results

type flagged_post poster_user flagger_user flag_created resolution assigned_user resolution_time (minutes)
off_topic Example_Post_Link1 example_user1 example_user2 2023-01-01 agreed example_user3 4.56
inappropriate Example_Post_Link2 example_user1 example_user2 2023-01-02 disagreed example_user3 38.76
spam Example_Post_Link3 example_user1 example_user2 2023-01-03 deferred example_user3 79.32
— — — — — — — —
1 Like