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 '2024%'

    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 '2024%'

    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;