Counts of editcounts (including deleted) – # of editors in a year

Fork of Counts of editcounts (including deleted) by Cryptic

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

Counts of registered users by edit count in 2024, grouped by powers of 10.  For example, users in cohort 1 had between 1 and 9 edits; users in cohort 1000 had between 1000 and 9999.

For [[WP:RAQ#Number of editors per year]] circa 5 January 2025.

SQL

WITH editcounts(ec_count, ec_name) AS
(
  SELECT COUNT(*), actor_name
  FROM revision
  JOIN actor_revision ON actor_id = rev_actor
  WHERE rev_timestamp LIKE '2009%'
    AND actor_user IS NOT NULL
  GROUP BY rev_actor
  
  UNION

  SELECT COUNT(*), actor_name
  FROM archive
  JOIN actor_archive ON actor_id = ar_actor
  WHERE ar_timestamp LIKE '2009%'
    AND actor_user IS NOT NULL
  GROUP BY ar_actor
),
editcountslog(ec_log10) AS
(
  SELECT FLOOR(LOG10(SUM(ec_count)))
  FROM editcounts
  GROUP BY ec_name
)
SELECT RPAD('1', ec_log10 + 1, '0') AS cohort, COUNT(*)
FROM editcountslog
GROUP BY ec_log10;

​x
 
WITH editcounts(ec_count, ec_name) AS(  SELECT COUNT(*), actor_name  FROM revision  JOIN actor_revision ON actor_id = rev_actor  WHERE rev_timestamp LIKE '2009%'    AND actor_user IS NOT NULL  GROUP BY rev_actor    UNION​  SELECT COUNT(*), actor_name  FROM archive  JOIN actor_archive ON actor_id = ar_actor  WHERE ar_timestamp LIKE '2009%'    AND actor_user IS NOT NULL  GROUP BY ar_actor),editcountslog(ec_log10) AS(  SELECT FLOOR(LOG10(SUM(ec_count)))  FROM editcounts  GROUP BY ec_name)SELECT RPAD('1', ec_log10 + 1, '0') AS cohort, COUNT(*)FROM editcountslogGROUP BY ec_log10;

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