USE metawiki_p;

SELECT

  page_create_year AS "Creation year",

  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"

FROM ( /* Derived table to define page_create_year */

  SELECT

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

    CASE WHEN EXISTS (

      SELECT 1

      FROM revision

      INNER JOIN user ON user_id = rev_user

      INNER JOIN comment ON comment_id = rev_comment_id

      WHERE 

        rev_page = page_id

        AND user_name = "E to the Pi times i"

        AND comment_text LIKE "Maintenace%"

    ) THEN 1 ELSE 0 END AS closed_by_me /* WIP */

  FROM revision

  INNER JOIN page ON page_id = rev_page

  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_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. */

  GROUP BY rev_page

) AS revision_augmented

GROUP BY page_create_year