cswiki analytics

SQL

/*
SELECT LEFT(rev_timestamp, 8) AS day, COUNT(rev_id),
	SUM(IF(actor_user IS NULL, 1, IF(DATE_ADD(rev_timestamp, INTERVAL -7 DAY) < log_timestamp, 1, 0))) AS is_new
FROM revision
JOIN page ON page_id = rev_page
JOIN actor_revision ON actor_id = rev_actor
LEFT JOIN user ON actor_user IS NOT NULL AND actor_user = user_id
LEFT JOIN logging_logindex
	ON actor_user IS NOT NULL
    AND log_type = 'newusers' AND log_action = 'create' AND log_namespace = 2 AND log_title = REPLACE(actor_name, ' ', '_')
    AND log_timestamp < rev_timestamp
WHERE page_namespace = 0
AND page_is_redirect = 0
AND rev_timestamp > '2020'
GROUP BY LEFT(rev_timestamp, 8);
*/
/*
SELECT DAYOFWEEK(rev_timestamp) AS den, RIGHT(LEFT(rev_timestamp, 10), 2) AS hodina, COUNT(rev_id) AS revertovano
FROM revision
JOIN change_tag ON rev_id = ct_rev_id AND ct_tag_id IN (SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-reverted')
WHERE rev_timestamp BETWEEN '2021' AND '2022'
GROUP BY DAYOFWEEK(rev_timestamp), RIGHT(LEFT(rev_timestamp, 10), 2);
*/
SELECT LEFT(rc_timestamp, 8) AS day,
	DAYOFWEEK(rc_timestamp) AS dayofweek,
    COUNT(rc_id) AS total, COUNT(ct_rc_id) / COUNT(rc_id) AS reverted_ratio,
    SUM(IF(rc_patrolled = 1, 1, 0)) / COUNT(rc_id) AS patrolled_ratio
FROM recentchanges
JOIN actor_recentchanges ON actor_id = rc_actor
LEFT JOIN change_tag ON rc_id = ct_rc_id AND ct_tag_id IN (SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-reverted')
WHERE actor_user IS NULL AND rc_source IN ('mw.edit', 'mw.new')
GROUP BY LEFT(rc_timestamp, 8)
ORDER BY LEFT(rc_timestamp, 8) DESC;
SQL

Checking query status...
