USE metawiki_p;

SELECT

  page_create_year AS "Creation year",

  /* Unnecessary: COUNT(*) AS "Requests created", */

  /* CASE statements here could probably be minified */

  COUNT(CASE WHEN NOT closed_by_me THEN 1 END) AS "Closed template added by others",

  COUNT(CASE WHEN closed_by_me THEN 1 END) AS "Closed template added by me",

  COUNT(CASE WHEN categorized_by_me THEN 1 END) AS "Categorized by me (undercounted)"

FROM ( /* Derived table to define page_create_year */

  SELECT

    SUBSTR(MIN(rev_timestamp),1,4) AS page_create_year,

    MAX(closed_by_me) AS closed_by_me,

    MAX(categorized_by_me) AS categorized_by_me

  FROM (

    SELECT

      rev_page,

      rev_timestamp,

      CASE WHEN

        user_name = "E to the Pi times i"

        AND comment_text LIKE "Maintenance: adding {{%"

      THEN 1 ELSE 0 END AS closed_by_me,

      /*

      Category additions undercount because determing if a category was added would require a byte delta in certain cases:

      [[Special:Diff/19094289]], [[Special:Diff/19094321]], [[Special:Diff/19094322]], [[Special:Diff/19094323/19094327]]

      */

      CASE WHEN

        user_name = "E to the Pi times i"

        AND (

          comment_text LIKE "Maintenance: adding renovated%"

          OR comment_text = "Fix miscategorized request."

        )

      THEN 1 END AS categorized_by_me

    FROM revision

    INNER JOIN page ON page_id = rev_page

    INNER JOIN user ON user_id = rev_user

    INNER JOIN comment ON comment_id = rev_comment_id

    WHERE

      page_namespace = 4

      AND NOT page_is_redirect

      AND page_title LIKE "Requests_for_%"

      AND (

        page_title LIKE "Requests_for_adminship/%"

        OR page_title LIKE "Requests_for_bot_status/%"

        OR page_title LIKE "Requests_for_bureaucratship/%"

        OR page_title LIKE "Requests_for_CentralNotice_adminship/%"

        OR page_title LIKE "Requests_for_centralnotice_adminship/%" /* NEW: 2 pages use this different capitalization */

        OR page_title LIKE "Requests_for_checkuser/%"

        OR page_title LIKE "Requests_for_interface_adminship/%"

        OR page_title LIKE "Requests_for_oversight/%"

        OR page_title LIKE "Requests_for_temporary_adminship/%"

        OR page_title LIKE "Requests_for_translation_adminship/%"

      )

      AND page_title != "Requests_for_adminship/Archives"

      AND page_title NOT LIKE "%Election_Committee" /* Hopefully, no will ever suffix their name with this and request permissions. */

  ) AS request_page_rev

  GROUP BY rev_page

) AS request_page

GROUP BY page_create_year