近期编辑ACG条目次数最多者 (zhwiki)

SQL

WITH pages_with_editing AS (
  SELECT
    rev_page,
    rev_actor
  FROM
    revision
  WHERE
    rev_timestamp >= 20220901000000
    AND rev_timestamp < 20221201000000
),
pages_vg_talk AS (
  SELECT
    page.page_namespace,
    page.page_title
  FROM
    page
    JOIN templatelinks ON tl_title = 'ACG專題'
    AND tl_from = page.page_id
),
pages_vg AS (
  SELECT
    page_main.page_id
  FROM
    page AS page_main
    JOIN pages_vg_talk ON page_main.page_title = pages_vg_talk.page_title
    AND page_main.page_namespace + 1 = pages_vg_talk.page_namespace
),
actor_with_editing AS (
  SELECT
    pages_with_editing.rev_actor,
    Count(*) AS cot
  FROM
    pages_with_editing,
    pages_vg
  WHERE
    pages_with_editing.rev_page = pages_vg.page_id
  GROUP BY
    pages_with_editing.rev_actor
)
SELECT
  CONCAT('* {{User|', actor.actor_name, '}} <small>[', actor_with_editing.cot, ']</small>') AS str
FROM
  actor_with_editing
  JOIN actor ON actor_with_editing.rev_actor = actor.actor_id
WHERE
  actor_with_editing.cot >= 5
ORDER BY
  actor_with_editing.cot DESC, actor.actor_name ASC
SQL

Checking query status...
