创建电子游戏专题条目最多的用户 (zhwiki)

SQL

WITH pages_vg AS (
  WITH pages_vg_talk AS (
    SELECT
      page.page_namespace,
      page.page_title
    FROM
      page
      JOIN categorylinks ON cl_to = '电子游戏条目'
      AND cl_type = 'page'
      AND cl_from = page.page_id
    WHERE
      page.page_namespace = 1
  )
  SELECT
    page.page_id
  FROM
    page
    JOIN pages_vg_talk ON page.page_title = pages_vg_talk.page_title
    AND page.page_namespace + 1 = pages_vg_talk.page_namespace
),
pages_vg_creation_revision AS (
  WITH first_revision AS (
    SELECTWITH pages_vg AS (
      WITH pages_vg_talk AS (
        SELECT
          page.page_namespace,
          page.page_title
        FROM
          page
          JOIN categorylinks ON cl_to = '电子游戏条目'
          AND cl_type = 'page'
          AND cl_from = page.page_id
        WHERE
          page.page_namespace = 1
      )
      SELECT
        page.page_id
      FROM
        page
        JOIN pages_vg_talk ON page.page_title = pages_vg_talk.page_title
        AND page.page_namespace + 1 = pages_vg_talk.page_namespace
    ),
    pages_vg_creation_revision AS (
      WITH first_revision AS (
        SELECT
          MIN(revision.rev_id) AS rev_id
        FROM
          revision
          JOIN pages_vg ON revision.rev_page = pages_vg.page_id
        WHERE
        GROUP BY
          revision.rev_page
      )
      SELECT
        revision.rev_page,
        revision.rev_actor,
        revision.rev_id
      FROM
        revision,
        first_revision
      WHERE
        revision.rev_id = first_revision.rev_id
        AND revision.rev_timestamp >= 20020101000000 -- 统计开始时间
        AND revision.rev_timestamp < 20230101000000 -- 统计结束时间
    )
    SELECT
      CONCAT(
        '* {{User|',
        actor.actor_name,
        '}} <small>[',
        COUNT(*),
        ']</small>'
      ) AS result
    FROM
      pages_vg_creation_revision
      JOIN actor ON pages_vg_creation_revision.rev_actor = actor.actor_id
    GROUP BY
      actor.actor_name
    ORDER BY
      COUNT(*) DESC,
      actor.actor_name ASC;MIN(revision.rev_id) AS rev_id
    FROM
      revision
      JOIN pages_vg ON revision.rev_page = pages_vg.page_id
    GROUP BY
      revision.rev_page
  )
  SELECT
    revision.rev_page,
    revision.rev_actor,
    revision.rev_id
  FROM
    revision,
    first_revision
  WHERE
    revision.rev_id = first_revision.rev_id
    AND revision.rev_timestamp >= 20020101000000 -- 统计开始时间
    AND revision.rev_timestamp < 20230101000000 -- 统计结束时间
)
SELECT
  CONCAT(
    '* {{User|',
    actor.actor_name,
    '}} <small>[',
    COUNT(*),
    ']</small>'
  ) AS result
FROM
  pages_vg_creation_revision
  JOIN actor ON pages_vg_creation_revision.rev_actor = actor.actor_id
GROUP BY
  actor.actor_name
ORDER BY
  COUNT(*) DESC,
  actor.actor_name ASC;
SQL

Checking query status...
