Untitled query #61673

SQL

# comments that start with AfD.
# See: https://en.wikipedia.org/wiki/Wikipedia:Articles_for_deletion#Nominating_article(s)_for_deletion
WITH afd_comments AS (
	SELECT
      comment_id
    FROM comment_revision
    WHERE
      comment_text LIKE 'AfD:%'
),
# Limit to just namespace 0 articles (no redirects)
articles AS (
  	SELECT
      page_id,
	  page_title
    FROM page
    WHERE
      page_namespace = 0
      AND NOT page_is_redirect
)
# List of articles with at least 2 edit summaries that seem to indicate the addition of an AfD template
SELECT
  page_title,
  COUNT(1) AS num_afd_comments
FROM revision_compat r
INNER JOIN afd_comments afd
  ON (r.rev_comment_id = afd.comment_id)
INNER JOIN articles a
  ON (r.rev_page = a.page_id)
GROUP BY
  page_title
HAVING
  num_afd_comments = 1
SQL

Checking query status...
