近期编辑电子游戏条目次数最多者 (zhwiki)

SQL

-- このSQL文はふたつクエリがある

/* 仅条目 */
WITH pages_vg AS (
  WITH pages_vg_talk AS (
    SELECT
      page.page_namespace,
      page.page_title
    FROM
      page
      JOIN categorylinks ON cl_to IN (
        '极高重要度电子游戏条目',
        '高重要度电子游戏条目',
        '中重要度电子游戏条目',
        '低重要度电子游戏条目',
        '未知重要度电子游戏条目'
      )
      AND cl_type = 'page'
      AND cl_from = page.page_id
  )
  SELECT
    page.page_id,
    page.page_title
  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
),
actor_with_editing AS (
  SELECT
    revision.rev_actor,
    COUNT(*) AS edits
  FROM
    pages_vg
    JOIN revision ON pages_vg.page_id = revision.rev_page
    AND revision.rev_timestamp >= 20220101000000 -- 统计开始时间
    AND revision.rev_timestamp < 20230101000000 -- 统计结束时间
  GROUP BY
    revision.rev_actor
)
SELECT
  actor.actor_name AS user_name, CONCAT(
    '* {{User|',
    actor.actor_name,
    '}}',
    ' <small>[',
    FORMAT(actor_with_editing.edits, 'N'),
    ']</small>'
  ) AS result
FROM
  actor_with_editing
  JOIN actor ON actor_with_editing.rev_actor = actor.actor_id
  AND actor_with_editing.edits >= 5
  AND actor.actor_user IS NOT NULL -- remove IP users
  AND actor.actor_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') -- remove bots
ORDER BY
  actor_with_editing.edits DESC,
  actor.actor_name ASC;

/* 含非条目 */
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
  )
  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
),
actor_with_editing AS (
  SELECT
    revision.rev_actor,
    COUNT(*) AS edits
  FROM
    pages_vg
    JOIN revision ON pages_vg.page_id = revision.rev_page
    AND revision.rev_timestamp >= 20220101000000 -- 统计开始时间
    AND revision.rev_timestamp < 20230101000000 -- 统计结束时间
  GROUP BY
    revision.rev_actor
)
SELECT
  actor.actor_name AS user_name, CONCAT(
    '* {{User|',
    actor.actor_name,
    '}}',
    ' <small>[',
    FORMAT(actor_with_editing.edits, 'N'),
    ']</small>'
  ) AS result
FROM
  actor_with_editing
  JOIN actor ON actor_with_editing.rev_actor = actor.actor_id
  AND actor_with_editing.edits >= 5
  AND actor.actor_user IS NOT NULL -- remove IP users
  AND actor.actor_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') -- remove bots
ORDER BY
  actor_with_editing.edits DESC,
  actor.actor_name ASC;
SQL

Checking query status...
