SQL
AخA
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;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.