Statistics on spam flags reported by AI triage automation

Data & reporting
, ,
1

This SQL query provides statistics about spam flags reported by the Discourse AI - AI triage automation. It calculates the total number of flags, how many were approved or rejected, and the corresponding percentages. This report is useful for understanding how effective automated spam detection is on your forum.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-05-30

SELECT 
    COUNT(*) AS total_flags,
    COUNT(*) FILTER (WHERE r.status = 1) AS approved_flags,
    COUNT(*) FILTER (WHERE r.status = 2) AS rejected_flags,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 1) / COUNT(*), 2) AS approved_percentage,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 2) / COUNT(*), 2) AS rejected_percentage
FROM 
    reviewables r
JOIN 
    post_actions pa ON pa.post_id = r.target_id AND r.target_type = 'Post'
WHERE
    pa.post_action_type_id = 8 -- ID for spam (Flag as spam and hide post)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Accepted and rejected flags
    AND r.created_by_id = -1 -- System user ID

SQL query explanation

  • Parameters: :start_date and :end_date parameters in YYYY-MM-DD format with default values.
  • Join: Connects the reviewables table with the post_actions table to ensure only spam flags that went through the review process are counted.
  • Filtering:
    • post_action_type_id = 8: Restricts to spam flags only.
    • Date range filtering with the specified parameters.
    • status IN (1, 2): Only includes flags that have been resolved (approved or rejected).
    • created_by_id = -1: Only includes flags created by the system user (AI triage).
  • Aggregations:
    • total_flags: The total number of spam flags by the system.
    • approved_flags: Count of flags that staff users approved (status = 1).
    • rejected_flags: Count of flags that staff users rejected (status = 2).
    • approved_percentage: Percentage of flags that were approved.
    • rejected_percentage: Percentage of flags that were rejected.

Example results

total_flags approved_flags rejected_flags approved_percentage rejected_percentage
152 128 24 84.21 15.79
2 Likes
2

And I got

PG::DivisionByZero: ERROR:  division by zero
3

That error happens because there were no spam flags by @system that you agreed or disagreed with during that time span.
The query tries to divide by the total number of flags that meet the conditions. In your case that is 0, so it breaks.
If you want the query to still return a result, you can use NULLIF to check for that so you get NULL when there’s nothing to divide.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-05-30

SELECT 
    COUNT(*) AS total_flags,
    COUNT(*) FILTER (WHERE r.status = 1) AS approved_flags,
    COUNT(*) FILTER (WHERE r.status = 2) AS rejected_flags,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 1) / NULLIF(COUNT(*), 0), 2) AS approved_percentage,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 2) / NULLIF(COUNT(*), 0), 2) AS rejected_percentage
FROM 
    reviewables r
JOIN 
    post_actions pa ON pa.post_id = r.target_id AND r.target_type = 'Post'
WHERE
    pa.post_action_type_id = 8 -- ID for spam (Flag as spam and hide post)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Accepted and rejected flags
    AND r.created_by_id = -1 -- System user ID
2 Likes
4

I’ve got a few, all false positives, but I can’t remember how I handled those.

But something like that I guessed too (I’m really bad at reading SQL).