Effectivity of abuse filters

SQL

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

Checking query status...
