Accounts created by year with 2023 blocks (0 edits)

Fork of Accounts created by year with 2023 blocks (> 0 edits) by BilledMammal

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

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, registration_year) AS
(
  SELECT user_name, LEFT(user_registration, 4)
  FROM user
  WHERE user_editcount = 0
)

SELECT COUNT(DISTINCT nu_name) AS 'accounts created in 2023, ever blocked', registration_year
FROM newusers
JOIN logging_logindex ON log_namespace = 2 AND log_title = nu_name
WHERE log_type = 'block'
  AND log_timestamp LIKE "2023%"
  AND log_action IN ('block', 'reblock')
  AND log_params LIKE '%"sitewide";b:1%'
GROUP BY registration_year
ORDER BY registration_year DESC

SQL

Checking query status...