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 inYYYY-MM-DD
format with default values. - Join: Connects the
reviewables
table with thepost_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 |