/*

​

This query used to work, so I'm unsure what broke it; it no longer correctly corrals my edits.

I suppose it is possible I accidentally edited it and broke it, but I am fairly sure this is not what occured.

I'll have to check my records for submitted queries from this.

​

*/

USE metawiki_p;

SELECT

  page_prefix AS "Request page prefix",

  /* 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_prefix */

  SELECT

    page_prefix,

    MAX(closed_by_me) AS closed_by_me,

    MAX(categorized_by_me) AS categorized_by_me

  FROM (

    SELECT

      CASE /* Unify capitalization; dunno if there's an easy way to remove regex redundancy 

      SQL user variables (starting with "@") may be the way to go, if I can determine how to get it to work.

      */

      WHEN REGEXP_SUBSTR(page_title, "^[^/]*") = "Requests_for_centralnotice_adminship" 

      THEN "Requests_for_CentralNotice_adminship" 

      ELSE REGEXP_SUBSTR(page_title, "^[^/]*")

      END AS page_prefix,

      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_prefix

ORDER BY page_prefix