SQL
AخA
SELECT COUNT(DISTINCT user_name) AS '#',
COUNT(DISTINCT IF(user_editcount >= 1, user_name, NULL)) AS '# ever edited',
COUNT(DISTINCT IF(log_id IS NOT NULL, user_name, NULL)) AS '# ever blocked',
COUNT(DISTINCT IF(COALESCE(log_timestamp, '') LIKE '2023%', user_name, NULL)) AS '# blocked in 2023',
COUNT(DISTINCT IF(user_editcount >= 1 AND log_id IS NOT NULL, user_name, NULL)) AS '# ever edited and ever blocked',
COUNT(DISTINCT IF(user_editcount >= 1 AND COALESCE(log_timestamp, '') LIKE '2023%', user_name, NULL)) AS '# ever edited and blocked in 2023'
FROM page
JOIN pagelinks ON pl_from = page_id
JOIN user ON user_name = REPLACE(pl_title, '_', ' ')
LEFT JOIN logging_logindex
ON log_namespace = 2 AND log_title = pl_title
AND log_type = 'block'
AND log_action IN ('block', 'reblock')
AND log_params LIKE '%"sitewide";b:1%'
WHERE page_namespace = 2
AND page_title = 'WhatamIdoing/Student_accounts_in_2023'
AND pl_namespace = 2;
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.