Statistics on spam flags reported by AI triage automation

Data & reporting
, ,
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