Random patroller chart data (Example)

Fork of Random patroller chart data (Example) by Lê Song Vĩ

This query is marked as a draft This query has been published by NewUniverse.

SQL

SELECT
  DAYOFWEEK(recentchanges_userindex.rc_timestamp) AS dow,
  HOUR(recentchanges_userindex.rc_timestamp) AS hour,
  COUNT(DISTINCT rc_id) AS edits
FROM
  recentchanges_userindex
  LEFT JOIN actor_logging ON actor_logging.actor_id = recentchanges_userindex.rc_actor
  LEFT JOIN change_tag ON change_tag.ct_rc_id = recentchanges_userindex.rc_id
  LEFT JOIN change_tag_def ON change_tag.ct_tag_id = change_tag_def.ctd_id
WHERE
  actor_logging.actor_name = "NewUniverse"
  AND (
    change_tag_def.ctd_name IN ("mw-rollback", "mw-undo")
    OR (
      recentchanges_userindex.rc_log_type LIKE "patrol"
    AND recentchanges_userindex.rc_log_action IN ("patrol", "autopatrol")
       )
  )
GROUP BY
  dow,
  hour
ORDER BY
  dow ASC,
  hour ASC;

SQL

Checking query status...