SQL
AخA
SELECT
actor_name as "Creator",
COUNT(*) AS "Year total",
SUM(CASE WHEN rev_timestamp LIKE '202201%' THEN 1 ELSE 0 END) AS "Jan",
SUM(CASE WHEN rev_timestamp LIKE '202202%' THEN 1 ELSE 0 END) AS "Feb",
SUM(CASE WHEN rev_timestamp LIKE '202203%' THEN 1 ELSE 0 END) AS "Mar",
SUM(CASE WHEN rev_timestamp LIKE '202204%' THEN 1 ELSE 0 END) AS "Apr",
SUM(CASE WHEN rev_timestamp LIKE '202205%' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN rev_timestamp LIKE '202206%' THEN 1 ELSE 0 END) AS "Jun",
SUM(CASE WHEN rev_timestamp LIKE '202207%' THEN 1 ELSE 0 END) AS "Jul",
SUM(CASE WHEN rev_timestamp LIKE '202208%' THEN 1 ELSE 0 END) AS "Aug",
SUM(CASE WHEN rev_timestamp LIKE '202209%' THEN 1 ELSE 0 END) AS "Sep",
SUM(CASE WHEN rev_timestamp LIKE '202210%' THEN 1 ELSE 0 END) AS "Oct",
SUM(CASE WHEN rev_timestamp LIKE '202211%' THEN 1 ELSE 0 END) AS "Nov",
SUM(CASE WHEN rev_timestamp LIKE '202212%' THEN 1 ELSE 0 END) AS "Dec"
FROM revision
JOIN actor_revision ON actor_id = rev_actor
JOIN page ON page_id = rev_page
JOIN comment ON rev_comment_id = comment_id
LEFT JOIN change_tag ON rev_id = ct_rev_id
AND (ct_tag_id = 11
OR ct_tag_id = 64) #redirects created by autowikibrowser aren't tagged as redirects
WHERE rev_parent_id = 0
AND page_namespace = 0
AND ct_tag_id IS NULL
AND rev_timestamp >= '2022'
AND rev_timestamp < '2023'
AND comment_text NOT LIKE "%(via [[WP:JWB]])"
GROUP BY actor_name
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC;
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.