Accounts created in 2023 with blocks

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

The number of accounts created on enwiki in 2023 who have ever made an edit, and the number of them who have ever had a sitewide block.

For [[WP:RAQ#Number of blocks last year]] circa 2 February 2024.

SQL

WITH newusers(nu_name) AS
(
  SELECT user_name
  FROM user
  WHERE user_registration LIKE '2023%'
    AND user_editcount >= 1
)
SELECT COUNT(DISTINCT nu_name) AS 'accounts created in 2023'
FROM newusers;

WITH newusers(nu_name) AS
(
  SELECT REPLACE(user_name, ' ', '_')
  FROM user
  WHERE user_registration LIKE '2023%'
    AND user_editcount >= 1
)
SELECT COUNT(DISTINCT nu_name) AS 'accounts created in 2023, ever blocked'
FROM newusers
JOIN logging_logindex ON log_namespace = 2 AND log_title = nu_name
WHERE log_type = 'block'
  AND log_action IN ('block', 'reblock')
  AND log_params LIKE '%"sitewide";b:1%';

​x
 
WITH newusers(nu_name) AS(  SELECT user_name  FROM user  WHERE user_registration LIKE '2023%'    AND user_editcount >= 1)SELECT COUNT(DISTINCT nu_name) AS 'accounts created in 2023'FROM newusers;​WITH newusers(nu_name) AS(  SELECT REPLACE(user_name, ' ', '_')  FROM user  WHERE user_registration LIKE '2023%'    AND user_editcount >= 1)SELECT COUNT(DISTINCT nu_name) AS 'accounts created in 2023, ever blocked'FROM newusersJOIN logging_logindex ON log_namespace = 2 AND log_title = nu_nameWHERE log_type = 'block'  AND log_action IN ('block', 'reblock')  AND log_params LIKE '%"sitewide";b:1%';

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...