#/users and #/afds on June 20 each year

For June 20 of each year, count:
* The total number of afd pages transcluded onto [[Wikipedia:Articles for deletion/Log/{{{year}}}_June_20]]
* The total number of different users who've edited at least one of those transcluded afd pages
* The average number of different users who've edited each of those transcluded afd pages.

Excludes bots (that have a bot flag).

SQL

WITH transcluded AS
(SELECT page_title AS dailylog_title, tl_title AS afd_title,
        (SELECT COUNT(DISTINCT rev_actor)
         FROM revision
         JOIN page AS afd_page ON page_id = rev_page
         LEFT JOIN actor_revision ON actor_id = rev_actor
         LEFT JOIN user_groups ON ug_user = actor_user AND ug_group = 'bot'
         WHERE page_namespace = 4
           AND page_title = tl_title
           AND ug_group IS NULL) AS users_per_afd
 FROM templatelinks
 JOIN page ON page_id = tl_from
 WHERE tl_namespace = 4
   AND (tl_title LIKE 'Articles\_for\_deletion/%'
        OR tl_title LIKE 'Votes\_for\_deletion/%')
   AND page_namespace = 4
   AND page_title LIKE 'Articles\_for\_deletion/Log/20__\_June\_20'
   AND tl_title != page_title -- don't include the self-transclusion that some template tricks use
)
SELECT MID(dailylog_title, 27, 4) AS 'afd year',
       COUNT(DISTINCT actor_id) AS 'total different users editing any afd',
       COUNT(DISTINCT afd_title) AS 'total afds transcluded to log page',
       AVG(users_per_afd) AS 'average number of different users editing each afd'
FROM page AS afd_page
JOIN transcluded ON afd_page.page_namespace = 4 AND afd_page.page_title = transcluded.afd_title
JOIN revision ON rev_page = afd_page.page_id
LEFT JOIN actor_revision ON actor_id = rev_actor
LEFT JOIN user_groups ON ug_user = actor_user AND ug_group = 'bot'
WHERE ug_group IS NULL
GROUP BY `afd year`;

SQL

Checking query status...