Untitled query #26458

SQL

USE cswiki_p;
SELECT day, changes, patrollable, patrollable / changes, reverts, reverts / patrollable FROM (
  SELECT LEFT(rc_timestamp, 8) AS day, COUNT(*) AS changes, SUM(IF(patrolusers.ug_user IS NULL, 1, 0)) AS patrollable, SUM(IF(
    rc_comment LIKE "% do předchozího stavu, jehož autorem je %"
    OR (
      rc_comment LIKE "Verze %"
      AND rc_comment LIKE "% uživatele %"
      AND rc_comment LIKE "% zrušena%"
    )
    OR (rc_comment LIKE "Revert%" AND rc_comment LIKE "%TW%"), 1, 0)) AS reverts
  FROM recentchanges
  LEFT JOIN (SELECT DISTINCT ug_user FROM user_groups WHERE ug_group IN ('autopatrolled', 'bot', 'sysop')) AS patrolusers
  ON rc_user = ug_user
  WHERE rc_source IN ('mw.edit', 'mw.new')
  GROUP BY LEFT(rc_timestamp, 8)
) AS subquery;
SQL

Checking query status...
