Analyzing Moderation and Flagging Activity Reports

SaraDev · February 13, 2025, 11:08pm

Maintaining a healthy and inclusive community requires effective moderation, which can include reviewing flagged posts, analyzing moderator performance, and managing user content.

This guide contains a variety of SQL reports for Discourse designed to help analyze moderation-related activities.

In this topic you will find detailed Data Explorer queries for:

Flag resolution statistics.
Reviewable resolution percentages.
Moderator-specific performance metrics.
Insights into user flagging activity.
Comprehensive data of all flagged user, post, and topic actions

Post Flag Resolution Percentage by Type

SQL Query Explanation

This query calculates the percentage of resolutions (agreed, disagreed, deferred, deleted) for flagged posts, grouped by flag type.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-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.deleted_at,
           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
),
flag_types AS (
    SELECT pat.id,
           CASE 
               WHEN pat.id = 3 THEN 'off_topic'
               WHEN pat.id = 4 THEN 'inappropriate'
               WHEN pat.id = 6 THEN 'notify_user'
               WHEN pat.id = 7 THEN 'notify_moderators'
               WHEN pat.id = 8 THEN 'spam'
           END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT
        pa.post_action_type_id,
        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'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.post_action_type_id
),
resolution_percentages AS (
    SELECT
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT
        flag_type,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_count ELSE 0 END) AS deleted_count,
        MAX(total_flags) AS total_flags
    FROM resolution_percentages
    GROUP BY flag_type
)
SELECT
    flag_type,
    agreed_percentage,
    agreed_count,
    disagreed_percentage,
    disagreed_count,
    deferred_percentage,
    deferred_count,
    deleted_percentage,
    deleted_count,
    total_flags
FROM pivoted_data
ORDER BY flag_type

Parameters Used

:start_date: The start date for filtering flagged posts.
:end_date: The end date for filtering flagged posts.

CTEs Explanation

period_actions: Filters flagged posts within the specified date range and calculates the time to resolution for each flag.
flag_types: Maps flag type IDs to human-readable names (e.g., off-topic, inappropriate, spam).
flag_resolutions: Groups flags by type and resolution (agreed, disagreed, deferred, deleted) and counts the occurrences of each resolution.
flag_totals: Calculates the total number of flags for each flag type.
resolution_percentages: Combines resolution counts and total flags to calculate the percentage of each resolution type for each flag type.
pivoted_data: Pivots the data to display resolution percentages and counts in separate columns for each resolution type.

Results Explanation

The final result is a table showing:

Flag type (e.g., off-topic, spam).
Percentages and counts for each resolution type (agreed, disagreed, deferred, deleted).
Total flags for each flag type.

Example Results

Flag Type	Agreed %	Agreed Count	Disagreed %	Disagreed Count	Deferred %	Deferred Count	Deleted %	Deleted Count	Total Flags
off_topic	50.00	25	30.00	15	10.00	5	10.00	5	50
spam	70.00	35	20.00	10	5.00	2	5.00	3	50

Reviewables Resolution Percentages

SQL Query Explanation

This query analyzes the resolution statuses of reviewable items (e.g., flagged posts) within a given date range. It calculates the percentage and count of each resolution status for each flag type.

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

WITH flag_data AS (
    SELECT
        r.type AS flag_type,
        r.status AS resolution_status,
        COUNT(*) AS flag_count
    FROM
        reviewables r
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        r.type,
        r.status
),
flag_totals AS (
    SELECT
        flag_type,
        SUM(flag_count) AS total_flags
    FROM
        flag_data
    GROUP BY
        flag_type
),
flag_percentages AS (
    SELECT
        fd.flag_type,
        fd.resolution_status,
        fd.flag_count,
        ROUND((fd.flag_count::decimal / ft.total_flags) * 100, 2) AS percentage
    FROM
        flag_data fd
    JOIN
        flag_totals ft
    ON
        fd.flag_type = ft.flag_type
)
SELECT
    fp.flag_type,
    -- Percentages
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.percentage END), 0) AS pending_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.percentage END), 0) AS approved_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.percentage END), 0) AS rejected_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.percentage END), 0) AS ignored_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.percentage END), 0) AS deleted_percentage,
    -- Counts
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.flag_count END), 0) AS pending_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.flag_count END), 0) AS approved_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.flag_count END), 0) AS rejected_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.flag_count END), 0) AS ignored_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.flag_count END), 0) AS deleted_count
FROM
    flag_percentages fp
GROUP BY
    fp.flag_type
ORDER BY
    fp.flag_type

Parameters Used

:start_date: The start date for filtering reviewables.
:end_date: The end date for filtering reviewables.

CTEs Explanation

flag_data: Groups reviewables by flag type and resolution status, counting the occurrences of each combination.
flag_totals: Calculates the total number of flags for each flag type.
flag_percentages: Combines flag counts and totals to calculate the percentage of each resolution status for each flag type.

Results Explanation

The final result is a table showing:

Flag type.
Percentages and counts for each resolution status (pending, approved, rejected, ignored, deleted).

Example Results

Flag Type	Pending %	Pending Count	Approved %	Approved Count	Rejected %	Rejected Count	Ignored %	Ignored Count	Deleted %	Deleted Count
off_topic	20.00	10	50.00	25	10.00	5	10.00	5	10.00	5
spam	10.00	5	70.00	35	10.00	5	5.00	2	5.00	3

Moderator Flag Resolutions

SQL Query Explanation

This query provides insights into moderator activity by showing which moderators resolved flagged posts, the types of flags they handled, and the resolutions they applied. It calculates percentages and counts for each resolution type.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

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.deleted_at,
        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
),
flag_types AS (
    SELECT 
        pat.id,
        CASE 
            WHEN pat.id = 3 THEN 'off_topic'
            WHEN pat.id = 4 THEN 'inappropriate'
            WHEN pat.id = 6 THEN 'notify_user'
            WHEN pat.id = 7 THEN 'notify_moderators'
            WHEN pat.id = 8 THEN 'spam'
        END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        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'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id
),
resolution_percentages AS (
    SELECT 
        fr.user_id,
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.user_id = fr.user_id AND ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT 
        rp.user_id,
        rp.flag_type,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_count ELSE 0 END) AS deleted_count,
        MAX(rp.total_flags) AS total_flags
    FROM resolution_percentages rp
    GROUP BY rp.user_id, rp.flag_type
)
SELECT 
    u.id AS user_id,
    u.username,
    p.flag_type,
    p.agreed_percentage,
    p.agreed_count,
    p.disagreed_percentage,
    p.disagreed_count,
    p.deferred_percentage,
    p.deferred_count,
    p.deleted_percentage,
    p.deleted_count,
    p.total_flags
FROM pivoted_data p
JOIN users u ON u.id = p.user_id
WHERE (:only_staff = false OR (u.admin = true OR u.moderator = true))
ORDER BY u.username, p.flag_type, p.total_flags

Parameters Used

:start_date: The start date for filtering flagged posts.
:end_date: The end date for filtering flagged posts.
:only_staff: A boolean parameter to filter results to include only staff members.

CTEs Explanation

period_actions: Filters flagged posts within the specified date range and calculates the time to resolution for each flag.
flag_types: Maps flag type IDs to human-readable names.
flag_resolutions: Groups flags by user, flag type, and resolution, counting the occurrences of each combination.
flag_totals: Calculates the total number of flags for each user and flag type.
resolution_percentages: Combines resolution counts and totals to calculate percentages for each resolution type.
pivoted_data: Pivots the data to display resolution percentages and counts in separate columns for each resolution type.

Results Explanation

The final result is a table showing:

Moderator username.
Percentages and counts for each resolution type (agreed, disagreed, deferred, deleted).
Total flags handled by each moderator.

Example Results

Moderator	Flag Type	Agreed %	Agreed Count	Disagreed %	Disagreed Count	Deferred %	Deferred Count	Deleted %	Deleted Count	Total Flags
mod1	off_topic	60.00	30	20.00	10	10.00	5	10.00	5	50
mod2	spam	70.00	35	20.00	10	5.00	2	5.00	3	50

Who is Flagging Posts

SQL Query Explanation

This query identifies the users who flagged posts within a specified date range and calculates the total number of flags submitted by each user.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

SELECT 
    u.id AS user_id,
    u.username,
    COUNT(pa.id) AS flag_count
FROM post_actions pa
JOIN users u ON u.id = pa.user_id
WHERE pa.post_action_type_id IN (3, 4, 6, 7, 8) -- flag types
  AND pa.created_at >= :start_date
  AND pa.created_at <= :end_date
  AND (:only_staff = false OR (u.admin = true OR u.moderator = true))
GROUP BY u.id, u.username
ORDER BY flag_count DESC, u.username
LIMIT 10

Parameters Used

:start_date: The start date for filtering flagged posts.
:end_date: The end date for filtering flagged posts.
:only_staff: A boolean parameter to filter results to include only staff members.

Results Explanation

The final result is a ranked list of users with their flag counts.

Example Results

User ID	Username	Flag Count
1	user1	50
2	user2	30
3	user3	20

User Notes

SQL Query Explanation

This query retrieves user notes stored in the plugin_store_rows table. It extracts details such as the user ID, creation date, note content, and the creator’s ID.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-01-01

WITH user_notes AS (

    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)

SELECT 
    un.user_id,
    un.created_at::date,
    un.user_note,
    un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at::date BETWEEN :start_date AND :end_date
ORDER BY created_at DESC

Parameters Used

:start_date: The start date for filtering user notes.
:end_date: The end date for filtering user notes.

Results Explanation

The final result is a detailed list of user notes with relevant metadata.

Example Results

User ID	Created At	User Note	Created By User ID
1	2025-01-01	This user is helpful.	2
2	2025-02-01	This user is associated with two other user accounts.	3

Moderator KPIs - Flags and Avg Flag Resolution Time

SQL Query Explanation

This query evaluates moderator performance by calculating the number of flags handled and the average resolution time (in minutes) for each moderator.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-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) -- Flag types
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
moderator_actions AS (
    SELECT pa.id,
           pa.post_id,
           pa.created_at,
           pa.responded_at,
           pa.time_to_resolution_minutes,
           COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) AS moderator_id
    FROM period_actions pa
    WHERE COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) IS NOT NULL
),
moderator_stats AS (
    SELECT
        m.moderator_id,
        u.username AS moderator_username,
        COUNT(m.id) AS handled_flags,
        AVG(m.time_to_resolution_minutes) AS avg_resolution_time_minutes
    FROM moderator_actions m
    JOIN users u ON u.id = m.moderator_id
    GROUP BY m.moderator_id, u.username
)
SELECT
    ms.moderator_username,
    ms.handled_flags,
    ROUND(ms.avg_resolution_time_minutes::numeric, 2) AS avg_resolution_time_minutes
FROM moderator_stats ms
ORDER BY ms.handled_flags DESC, ms.avg_resolution_time_minutes ASC

Parameters Used

:start_date: The start date for filtering flagged posts.
:end_date: The end date for filtering flagged posts.

CTEs Explanation

period_actions: Filters flagged posts within the specified date range and calculates the time to resolution for each flag.
moderator_actions: Identifies flags resolved by moderators and calculates the time to resolution for each flag.
moderator_stats: Groups flags by moderator and calculates the total number of flags handled and the average resolution time.

Results Explanation

The final result is a ranked list of moderators with their handled flag counts and average resolution times.

Example Results

Moderator Username	Handled Flags	Avg Resolution Time (Minutes)
mod1	50	15.00
mod2	30	20.00

All Flag Data

SQL Query Explanation

This query provides a comprehensive dataset of all flagged user, post, and topic data within a specified date range. It combines data from multiple tables to include details such as the flag type, flagged item, flag reason, flag source, resolution decision, and related messages.

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        r.potentially_illegal AS potentially_illegal, 
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        p.hidden_at AS post_hidden_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        --AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
review_decisions AS (
    SELECT
        0 AS status_code, 'pending' AS decision_name
    UNION ALL
    SELECT
        1 AS status_code, 'agreed' AS decision_name
    UNION ALL
    SELECT
        2 AS status_code, 'disagreed' AS decision_name
    UNION ALL
    SELECT
        3 AS status_code, 'ignored' AS decision_name
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'off_topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'illegal' AS flag_type_name
)
SELECT
    fd.flag_id,
    fd.post_id AS flagged_item,
    fd.flagged_by_username,
    fd.flagged_date,
    fd.flag_type,
    ft.flag_type_name AS flag_type_name,
    fd.flag_source AS reviewable_by_moderator,
    fd.flag_reason,
    fd.flagged_item_text,
    pa.related_post_id AS related_message_id_post_id,
    regexp_replace(rp.raw, '(https?://[^\s]+)', '', 'g') AS related_message_text, -- Removes only URLs
    fd.reviewed_at,
    fd.reviewed_by_username AS reviewed_by,
    rd.decision_name AS review_decision,
    CASE 
        WHEN fd.user_silenced_till IS NOT NULL THEN 'User silenced'
        WHEN fd.user_suspended_till IS NOT NULL THEN 'User suspended'
        WHEN fd.post_deleted_at IS NOT NULL THEN 'Post deleted'
        WHEN fd.post_hidden_at IS NOT NULL THEN 'Post hidden'
        ELSE 'No action taken'
    END AS action_taken,
    CASE 
        WHEN fd.reviewed_at IS NOT NULL THEN ROUND(EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date)) / 60, 2)
        ELSE NULL
    END AS review_time_minutes -- Time difference in minutes
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN posts rp 
    ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd 
    ON fd.review_status = rd.status_code
ORDER BY
    fd.flagged_date DESC

Parameters Used

:start_date: The start date for filtering flagged posts.
:end_date: The end date for filtering flagged posts.

CTEs Explanation

flag_data: Retrieves detailed information about flagged posts, including the flag ID, flagged item, flag type, flag reason, flag source, and review details.
review_decisions: Maps review status codes to human-readable decision names (e.g., pending, agreed, disagreed, ignored).
flag_types: Maps post action type IDs to human-readable flag type names (e.g., off-topic, inappropriate, spam).

Results Explanation

Flag ID: The unique identifier for the flag.
Flagged Item: The ID of the flagged post.
Flagged By Username: The username of the user who flagged the post.
Flagged Date: The date when the flag was created.
Flag Type: The numeric type of the flag.
Flag Type Name: The human-readable name of the flag type (e.g., off-topic, spam).
Reviewable By Moderator: Indicates whether the flag was raised by a user or the system.
Flag Reason: The reason provided for the flag.
Flagged Item Text: The content of the flagged post.
Related Message ID: The ID of any related message (if applicable).
Related Message Text: The content of the related message, with URLs removed for clarity.
Reviewed By Username: The username of the moderator who reviewed the flag.
Review Decision: The decision made by the reviewer (e.g., agreed, disagreed, ignored, deleted).
Action Taken: The action taken as a result of the flag, such as silencing or suspending the user, deleting or hiding the post, or no action.
Review Time (Minutes): The time taken to review the flag, calculated as the difference between the flag creation time and the review time, in minutes.

Example Results (Anonymized)

Flag ID	Flagged Item	Flagged By Username	Flagged Date	Flag Type	Flag Type Name	Reviewable By Moderator	Flag Reason	Flagged Item Text	Related Message ID	Related Message Text	Reviewed By Username	Review Decision	Action Taken	Review Time (Minutes)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Spam content	“Buy now at spam.com”	98765	“Check this out!”	mod456	Agreed	Post deleted	15.25
12346	67891	user124	2025-04-02 14:30	4	Inappropriate	false	Offensive	“This is inappropriate!”	NULL	NULL	mod457	Disagreed	No action taken	30.50

Flag Reports Submitted

This report provides an overview of the flags submitted within the specified date range. It categorizes flags by their type (e.g., Spam, Inappropriate) and distinguishes between user-reported flags and system-generated flags. The report includes the total number of flags for each type, helping to identify the most common issues flagged on the platform.

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Something else' AS flag_type_name
)
SELECT
    ft.flag_type_name AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
GROUP BY
    ft.flag_type_name
ORDER BY
    Total DESC

Parameters Used

:start_date: The start date for filtering flagged posts.
:end_date: The end date for filtering flagged posts.

CTEs Explanation

flag_data:
This CTE retrieves detailed information about flagged posts, including:

The flag’s unique ID (flag_id), the flagged post’s ID (post_id), and the topic it belongs to (topic_id).
Information about the flag itself, such as the user who flagged it (flagged_by_username), the date it was flagged (flagged_date), the type of flag (flag_type), and the reason for the flag (flag_reason).
Details about the review process, including the moderator who reviewed the flag (reviewed_by_username), the review decision (review_status), and the time of review (reviewed_at).

flag_types:
This CTE maps numeric post action type IDs to human-readable flag type names:

3: Off-topic
4: Inappropriate
6: Notify user
7: Notify moderators
8: Spam
10: Illegal
NULL: Something else

Results Explanation

The final query aggregates the flag data by flag type and provides the following metrics:

Type: The human-readable name of the flag type (e.g., Off-topic, Spam).
Reported: The count of flags submitted by users (excluding system-generated flags).
Automated: The count of flags generated by the system or bots (e.g., spam_scanner_bot, system).
Total: The total number of flags for each type.

The results are sorted by the total number of flags in descending order.

Example Results

Type	Reported	Automated	Total
Spam	120	80	200
Inappropriate	90	10	100
Off-topic	60	5	65
Notify_moderators	30	0	30
Illegal	10	2	12
Something else	5	0	5

Bans and Suspensions:

This report lists users who were suspended or silenced within the specified date range. It includes details such as the suspension or silence dates, the duration of the action, and the user’s account creation and last activity dates. This report is useful for monitoring moderation actions and identifying patterns in user behavior that lead to bans or silences.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2026-01-01
SELECT 
    u.id AS user_id,
    u.username,
    u.name,
    u.suspended_at,
    u.suspended_till,
    u.silenced_till,
    u.created_at AS account_created_at,
    u.last_seen_at,
    u.flag_level,
    u.admin,
    u.moderator
FROM 
    users u
WHERE 
    (
        u.suspended_at BETWEEN :start_date AND :end_date
        OR u.silenced_till BETWEEN :start_date AND :end_date
    )
ORDER BY 
    u.suspended_at DESC NULLS LAST,
    u.silenced_till DESC NULLS LAST

Parameters Used

:start_date: The start date for filtering suspended or silenced users.
:end_date: The end date for filtering suspended or silenced users.

Results Explanation

This query retrieves a list of users who were suspended or silenced within the specified date range. Key columns include:

User ID: The unique identifier for the user.
Username: The username of the user.
Name: The full name of the user (if available).
Suspended At: The date when the user was suspended.
Suspended Till: The date until which the user is suspended.
Silenced Till: The date until which the user is silenced.
Account Created At: The date when the user’s account was created.
Last Seen At: The last time the user was active on the platform.
Flag Level: The user’s current flag level.
Admin: Whether the user is an admin (true/false).
Moderator: Whether the user is a moderator (true/false).

The results are sorted by the suspension date (suspended_at) and silence date (silenced_till) in descending order, with null values appearing last.

Example Results

User ID	Username	Name	Suspended At	Suspended Till	Silenced Till	Account Created At	Last Seen At	Flag Level	Admin	Moderator
101	user123	John Doe	2025-03-15 10:00	2025-04-15 10:00	NULL	2020-01-01 12:00	2025-03-14 18:00	2	false	false
102	user456	Jane Smith	NULL	NULL	2025-03-20 18:00	2021-06-10 15:00	2025-03-19 20:00	1	false	false
103	mod789	Moderator1	2025-02-01 08:00	2025-03-01 08:00	NULL	2019-05-05 10:00	2025-01-31 22:00	3	false	true
104	admin001	AdminUser	NULL	NULL	2025-03-25 12:00	2018-12-25 09:00	2025-03-24 16:00	0	true	false

Agreed Flag Actions Taken

This report focuses on flags that were agreed upon by moderators and resulted in actions being taken. It categorizes flags by type and provides metrics such as the total number of flags, the median time taken to act on them, and the outcomes (e.g., users silenced, posts deleted).

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
    SELECT
        COALESCE(ft.flag_type_name, fd.flag_type) AS flag_type,
        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        fd.reviewed_at IS NOT NULL
        AND (
            ft.flag_type_name IS NOT NULL -- Include mapped flag types
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Include specific flag types for NULL results
        )
    GROUP BY
        COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
    COALESCE(ft.flag_type_name, fd.flag_type) AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total,
    COALESCE(mta.median_time_minutes, 0) AS "Median time to act (minutes)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"

FROM
    flag_data fd
LEFT JOIN flag_types ft 
    ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta 
    ON COALESCE(ft.flag_type_name, fd.flag_type) = mta.flag_type
WHERE
    ft.flag_type_name IS NOT NULL -- Include mapped flag types
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Include specific flag types for NULL results
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

Parameters Used

:start_date: The start date for filtering agreed flags.
:end_date: The end date for filtering agreed flags.

CTEs Explanation

flag_data:
This CTE retrieves detailed information about flags that were agreed upon and had actions taken. It includes:

The flag’s unique ID (flag_id), the flagged post’s ID (post_id), and the topic it belongs to (topic_id).
Information about the flag itself, such as the user who flagged it (flagged_by_username), the date it was flagged (flagged_date), the type of flag (flag_type), and the reason for the flag (flag_reason).
Details about the review process, including the moderator who reviewed the flag (reviewed_by_username), the review decision (review_status), and the time of review (reviewed_at).
Additional information about the flagged post, such as whether it was deleted, hidden, or if the author was silenced or suspended.

flag_types:
This CTE maps numeric post action type IDs to human-readable flag type names:

3: Off-topic
4: Inappropriate
6: Notify user
7: Notify moderators
8: Spam
10: Illegal

median_time_to_act:
This CTE calculates the median time (in minutes) taken to act on each flag type. The time is calculated as the difference between the flag creation time (flagged_date) and the review time (reviewed_at).

Results Explanation

The final query aggregates the agreed flag data by flag type and provides the following metrics:

Type: The human-readable name of the flag type (e.g., Off-topic, Spam).
Reported: The count of flags submitted by users (excluding system-generated flags).
Automated: The count of flags generated by the system or bots (e.g., spam_scanner_bot, system).
Total: The total number of flags for each type.
Median Time to Act (Minutes): The median time taken to act on flags of this type, in minutes.
User Silenced: The count of flags that resulted in the user being silenced.
User Deleted: The count of flags that resulted in the user being suspended.
Post Deleted: The count of flags that resulted in the post being deleted.
Post Hidden: The count of flags that resulted in the post being hidden.

The results are sorted by the total number of flags in descending order.

Example Results

Type	Reported	Automated	Total	Median Time to Act (Minutes)	User Silenced	User Deleted	Post Deleted	Post Hidden
Spam	100	50	150	30	20	10	50	30
Inappropriate	80	5	85	45	15	5	30	20
Off-topic	40	2	42	25	5	0	10	15
Notify_moderators	20	0	20	60	0	0	5	10
Illegal	5	1	6	120	1	1	3	2

Moderation Actions Taken

This report provides a summary of moderation actions taken within the specified date range. It aggregates various types of agreed actions, including content flagged by users or automation, posts deleted or hidden, warnings issued, accounts deleted or suspended, and users silenced for extended periods. Each category is presented with the total number of cases, offering a high-level overview of moderation activity.

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS user_flagged,
        COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS automation_flagged
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL -- Include mapped flag types
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Include specific flag types for NULL results
),
warnings_issued AS (
    SELECT
        COUNT(*) AS warnings_count
    FROM
        user_warnings
    WHERE
        created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        COUNT(CASE 
            WHEN uh.action = 1 
                AND (
                    LOWER(uh.context) LIKE '%deleted via review queue%' OR
                    LOWER(uh.context) LIKE '%to be a spammer%' OR
                    LOWER(uh.context) LIKE '%review%' OR
                    LOWER(uh.context) LIKE '%reviewable user rejected%'
                ) 
            THEN 1 
        END) AS accounts_deleted,
        COUNT(CASE WHEN uh.action = 10 THEN 1 END) AS accounts_suspended
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
),
posts_deleted_and_hidden AS (
    SELECT
        COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS posts_deleted,
        COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS posts_hidden
    FROM
        flag_data fd
),
silences_issued AS (
    SELECT
        COUNT(*) AS silences_count
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    'Content flagged by users' AS category,
    fc.user_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Content flagged by automation' AS category,
    fc.automation_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Posts deleted for violating terms' AS category,
    pdh.posts_deleted AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Posts hidden' AS category,
    pdh.posts_hidden AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Warnings Issued' AS category,
    wi.warnings_count AS "Number of Cases"
FROM warnings_issued wi

UNION ALL

SELECT
    'Accounts deleted' AS category,
    vs.accounts_deleted AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Accounts suspended' AS category,
    vs.accounts_suspended AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Users silenced for 10+ years' AS category,
    si.silences_count AS "Number of Cases"
FROM silences_issued si

Parameters Used

:start_date: The start date for filtering moderation actions.
:end_date: The end date for filtering moderation actions.

Results Explanation

The query aggregates moderation actions into categories and provides the total number of cases for each category. The categories include:

Content flagged by users: The number of posts flagged by regular users (excluding system-generated flags).
Content flagged by automation: The number of posts flagged by automated systems or bots (e.g., spam_scanner_bot, system).
Posts deleted for violating terms: The number of posts that were deleted due to violations of community guidelines or terms of service.
Posts hidden: The number of posts that were hidden (but not deleted) for various reasons.
Warnings Issued: The number of warnings issued to users for inappropriate behavior or content.
Accounts deleted: The number of user accounts deleted due to violations, such as being flagged as spammers or rejected in review queues.
Accounts suspended: The number of user accounts suspended for a specific period due to violations.
Users silenced for 10+ years: The number of users silenced indefinitely or for extended periods (10+ years).

Example Results

Category	Number of Cases
Content flagged by users	150
Content flagged by automation	100
Posts deleted for violating terms	50
Posts hidden	30
Warnings Issued	20
Accounts deleted	10
Accounts suspended	15
Users silenced for 10+ years	5

Individual Moderation Actions Taken

This report provides a detailed log of agreed individual moderation actions taken within the specified date range. It includes information about the user who performed the action, the target user, the date of the action, the category of the action (e.g., flagged content, deleted posts, issued warnings), and the context or reason for the action. This report is useful for auditing specific moderation decisions and understanding the context behind each action.

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by users' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system')
    
    UNION ALL

    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by automation' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username IN ('spam_scanner_bot', 'system')
),
posts_deleted_and_hidden AS (
    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_deleted_at AS action_date,
        'Posts deleted for violating terms' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_deleted_at IS NOT NULL
    
    UNION ALL

    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_hidden_at AS action_date,
        'Posts hidden' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_hidden_at IS NOT NULL
),
warnings_issued AS (
    SELECT
        CAST(uw.created_by_id AS TEXT) AS acting_user,
        CAST(uw.user_id AS TEXT) AS target_user,
        uw.created_at AS action_date,
        'Warnings Issued' AS category,
        NULL AS context
    FROM
        user_warnings uw
    WHERE
        uw.created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts deleted' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 1
        AND (
            LOWER(uh.context) LIKE '%deleted via review queue%' OR
            LOWER(uh.context) LIKE '%to be a spammer%' OR
            LOWER(uh.context) LIKE '%review%' OR
            LOWER(uh.context) LIKE '%reviewable user rejected%'
        )
    
    UNION ALL

    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts suspended' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 10
),
silences_issued AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Users silenced for 10+ years' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM flagged_content

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM posts_deleted_and_hidden

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM warnings_issued

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM violations_and_suspensions

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM silences_issued

Parameters Used

:start_date: The start date for filtering individual moderation actions.
:end_date: The end date for filtering individual moderation actions.

Results Explanation

The query provides a detailed log of individual moderation actions, including:

Acting User: The username of the moderator, system, or user who performed the action.
Target User: The username or ID of the user who was the subject of the action (e.g., the author of a flagged post or the recipient of a warning).
Action Date: The date and time when the action occurred.
Category: The type of moderation action, such as:

Content flagged by users
Content flagged by automation
Posts deleted for violating terms
Posts hidden
Warnings Issued
Accounts deleted
Accounts suspended
Users silenced for 10+ years

Context: Additional information or content related to the action, such as the text of a flagged post or the reason for a suspension.

Example Results

Acting User	Target User	Action Date	Category	Context
user123	user456	2024-02-01 10:00	Content flagged by users	“This post contains spam content.”
spam_scanner	user789	2024-02-02 12:00	Content flagged by automation	“Detected as spam by system.”
mod001	user456	2024-02-03 14:00	Posts deleted for violating terms	“Example Post Content
mod002	user123	2024-02-04 16:00	Posts hidden	“Post deemed inappropriate.”
admin001	user789	2024-02-05 18:00	Warnings Issued	NULL
admin002	user456	2024-02-06 20:00	Accounts deleted	“Account deleted via review queue.”
mod003	user123	2024-02-07 22:00	Accounts suspended	“User suspended for repeated spam.”
admin003	user789	2024-02-08 08:00	Users silenced for 10+ years	“User silenced for extreme violations.”

Topic		Replies	Views
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	377	December 22, 2023
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	6	544	February 27, 2025
Solved Topic and Moderator Response Stats by Category Data & reporting solved , sql-query	0	253	April 3, 2024
Discourse Moderation Guide Site Management moderation , explanation	1	261410	December 4, 2024
Moderator Flag Handling Efficiency Report Data & reporting sql-query	0	165	March 28, 2024

Analyzing Moderation and Flagging Activity Reports

Post Flag Resolution Percentage by Type

SQL Query Explanation

Parameters Used

CTEs Explanation

Results Explanation

Example Results

Reviewables Resolution Percentages

SQL Query Explanation

Parameters Used

CTEs Explanation

Results Explanation

Example Results

Moderator Flag Resolutions

SQL Query Explanation

Parameters Used

CTEs Explanation

Results Explanation

Example Results

Who is Flagging Posts

SQL Query Explanation

Parameters Used

Results Explanation

Example Results

User Notes

SQL Query Explanation

Parameters Used

Results Explanation

Example Results

Moderator KPIs - Flags and Avg Flag Resolution Time

SQL Query Explanation

Parameters Used

CTEs Explanation

Results Explanation

Example Results

All Flag Data

SQL Query Explanation

Parameters Used

CTEs Explanation

Results Explanation

Example Results (Anonymized)

Example Results (Anonymized)

Flag Reports Submitted

Parameters Used

CTEs Explanation

Results Explanation

Example Results

Bans and Suspensions:

Parameters Used

Results Explanation

Example Results

Agreed Flag Actions Taken

Parameters Used

CTEs Explanation

Results Explanation

Example Results

Moderation Actions Taken

Parameters Used

Results Explanation

Example Results

Individual Moderation Actions Taken

Parameters Used

Results Explanation

Example Results

Related topics