Effectivity of abuse filters

SQL

USE cswiki_p;
SELECT afl_filter, SUM(IF(afl_actions LIKE '%warn%', 1, 0)) - SUM(IF(afl_actions LIKE '%warn%', 0, 1)), COUNT(*) AS hits
FROM abuse_filter_log
WHERE afl_filter NOT LIKE 'global-%'
AND EXISTS (SELECT * FROM abuse_filter_action WHERE afa_filter = afl_filter AND afa_consequence = 'warn')
AND afl_timestamp >= (
  SELECT afl_timestamp FROM abuse_filter_log AS afl1
  WHERE afl1.afl_filter = afl_filter AND afl1.afl_actions LIKE '%warn%' ORDER BY afl1.afl_timestamp LIMIT 1
)
GROUP BY afl_filter
#ORDER BY ratio;
SQL

Checking query status...
