Articles by year (all, bot-created) on skwiki

SQL

-- USE skwiki_p;

SELECT
    YEAR(rf.rev_timestamp) AS year,
    COUNT(*) AS count,
    SUM(COUNT(*)) OVER(ORDER BY YEAR(rf.rev_timestamp)) AS count_cumulative
  FROM
    page p, revision rf
  WHERE
    rf.rev_page = p.page_id and rf.rev_parent_id = 0 and
    p.page_namespace = 0 and
    p.page_is_redirect = 0
  GROUP BY
    YEAR(rf.rev_timestamp)
  ORDER BY
    YEAR(rf.rev_timestamp)
  ;
  
SELECT
    YEAR(rf.rev_timestamp) AS year,
    COUNT(*) as count,
    SUM(COUNT(*)) OVER(ORDER BY YEAR(rf.rev_timestamp)) AS count_cumulative
  FROM
    page p, revision rf, actor rfa
  WHERE
    rf.rev_page = p.page_id and rf.rev_parent_id = 0 and
    rfa.actor_id = rf.rev_actor and
    p.page_namespace = 0 and
    p.page_is_redirect = 0 and
    (rfa.actor_user in (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') or rfa.actor_name RLIKE '^(Wizzo-Bot|IW-BOT|Atomobot|Liso-Bot|Rainbot|TeslaBot|Adrian-Bot)$')
    -- rf.rev_user_text = 'Wizzo-Bot'
    -- LOWER(CONVERT(rf.rev_user_text USING latin1)) RLIKE '^bot|bot$|mediawiki message delivery|commonsdelinker|commonsticker|robotquistnix|loveless|klbot2'
  GROUP BY
    YEAR(rf.rev_timestamp)
  ORDER BY
    YEAR(rf.rev_timestamp)
  ;
SQL

Checking query status...
