Indef protected user talk pages

Fork of Indef protected user talk pages by Fæ

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

Breakdown of indef protected user talk pages. The statistics show that there has been a distinct shift in admin practice over the last 10 years. The current default practice is to avoid indef protections unless there are significant related risks of disruption such as known legal issues or harassment. Raised on https://commons.wikimedia.org/wiki/Commons:Administrators%27_noticeboard#Protection_of_blocked_user_talk_pages_without_specific_cause

Using the talk page protecting admin by checking with the logging table caused problems with this SQL. It appears that the mass renaming of accounts during global SUL harmonization has made tracking back through the log problematic. The admin account shown is the admin that blocked the account but may not always be the same as the account protecting the talk page.

SQL

USE commonswiki_p;
SELECT
  LEFT(log_timestamp,4) AS Year,
  COUNT(DISTINCT page_title) AS Total,
  GROUP_CONCAT(DISTINCT page_title, ' (', ipb_by_text,')' SEPARATOR '; ') AS Pages
FROM page_restrictions
JOIN page ON pr_page = page_id
WHERE pr_type = 'edit'
  AND pr_level = 'sysop'
  AND pr_expiry = 'infinity'
  AND page_namespace = 3
  AND NOT page_title LIKE '%/%'
GROUP BY LEFT(log_timestamp,4) DESC
;

AخA
 
USE commonswiki_p;SELECT  LEFT(log_timestamp,4) AS Year,  COUNT(DISTINCT page_title) AS Total,  GROUP_CONCAT(DISTINCT page_title, ' (', ipb_by_text,')' SEPARATOR '; ') AS PagesFROM page_restrictionsJOIN page ON pr_page = page_idWHERE pr_type = 'edit'  AND pr_level = 'sysop'  AND pr_expiry = 'infinity'  AND page_namespace = 3  AND NOT page_title LIKE '%/%'GROUP BY LEFT(log_timestamp,4) DESC;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...