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",

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

  user_name

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,

    user_name

    /*

    Still WIP, 

    I would like to split into a subquery the return two separate columns:

    one for category additions, one for templates (not mutually exclusive)

    */

  FROM (

    SELECT

      rev_page,

      rev_timestamp,

      user_name,

      CASE WHEN

        user_name = "E to the Pi times i"

      /*  AND comment_text = "Maintenance: adding {{[[Help:Substitution|subst]]:[[Template:Closed request top|Closed request top]]}} and {{[[Help:Substitution|subst]]:[[Template:Closed request bottom|Closed request bottom]]}} to old closed request." 

      */THEN 1 ELSE 0 END AS closed_by_me,

      CASE WHEN

        user_name = "E to the Pi times i"

      /*  AND comment_text = "Maintenance: adding renovated {{[[Help:Substitution|subst]]:[[Template:Closed request bottom|Closed request bottom]]}} to categorize closed request."

      */THEN 1 ELSE 0 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_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