-- 1

SELECT COUNT(*), COALESCE(ug_group, 'non-autoreviewer')

FROM revision

STRAIGHT_JOIN page ON page_id = rev_page

LEFT JOIN page_props ON pp_page = page_id AND pp_value = 'disambiguation'

JOIN actor_revision ON actor_id = rev_actor

LEFT JOIN user_groups ON ug_user = actor_user AND ug_group = 'autoreviewer'

WHERE rev_timestamp BETWEEN '202501' AND '202502'

  AND rev_parent_id = 0

  AND page_is_redirect = 0

  AND page_namespace = 0

  AND pp_page IS NULL

GROUP BY 2;

​

-- 2

SELECT COUNT(*), CASE WHEN extendedconfirmed.ug_group IS NULL AND sysop.ug_group IS NULL THEN 'non-extendedconfirmed' ELSE 'extendedconfirmed' END AS 'group'

FROM revision

STRAIGHT_JOIN page ON page_id = rev_page

JOIN actor_revision ON actor_id = rev_actor

LEFT JOIN user_groups AS extendedconfirmed ON extendedconfirmed.ug_user = actor_user AND extendedconfirmed.ug_group = 'extendedconfirmed'

LEFT JOIN user_groups AS sysop ON sysop.ug_user = actor_user AND sysop.ug_group = 'sysop'

WHERE rev_timestamp BETWEEN '202501' AND '202502'

  AND rev_parent_id = 0

  AND page_namespace = 0

GROUP BY 2;

​

-- 3

SELECT COUNT(*), CASE WHEN ptrp_page_id IS NULL THEN 'unpatrolled' ELSE 'patrolled' END AS 'patrol status'

FROM revision

STRAIGHT_JOIN page ON page_id = rev_page

LEFT JOIN page_props ON pp_page = page_id AND pp_value = 'disambiguation'

LEFT JOIN pagetriage_page ON ptrp_page_id = page_id AND ptrp_reviewed IN (2, 3)

WHERE rev_timestamp BETWEEN '202501' AND '202502'

  AND rev_parent_id = 0

  AND page_is_redirect = 0

  AND page_namespace = 0

  AND pp_page IS NULL

GROUP BY 2;

​

-- 4

SELECT COUNT(*), CASE WHEN page_id IS NULL THEN 'redlink' ELSE 'bluelink' END AS 'exists?'

FROM logging

LEFT JOIN page ON page_namespace = 0 AND page_title = log_title

WHERE log_type = 'create'

  AND log_timestamp BETWEEN '202501' AND '202502'

  AND log_namespace = 0

GROUP BY 2;

​

-- 5

SELECT COUNT(*),

       EXISTS (SELECT 1

               FROM logging_logindex AS del

               WHERE del.log_type = 'delete'

                 AND del.log_action = 'delete'

                 AND del.log_namespace = 0

                 AND del.log_title = creation.log_title

                 AND del.log_timestamp < creation.log_timestamp) AS 'previously deleted'

FROM logging AS creation

WHERE log_type = 'create'

  AND log_timestamp BETWEEN '202501' AND '202502'

  AND log_namespace = 0

GROUP BY 2;

​

-- 6

SELECT COUNT(*) AS 'total redirect removals', COUNT(DISTINCT page_title) AS 'pages with at least one redirect removal'

FROM revision

JOIN change_tag ON ct_rev_id = rev_id

JOIN change_tag_def ON ctd_id = ct_tag_id

STRAIGHT_JOIN page ON page_id = rev_page

WHERE rev_timestamp BETWEEN '202501' AND '202502'

  AND page_namespace = 0

  AND ctd_name = 'mw-removed-redirect';