Untitled query #70136

SQL

USE zhwiki_p;

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
    WHERE
      page.page_namespace = 1
  )
  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
),
pages_vg_revision AS (
  WITH page_and_editors AS (
    SELECT
      DISTINCT pages_vg.page_title,
      revision.rev_actor
    FROM
      revision
      JOIN pages_vg ON revision.rev_page = pages_vg.page_id
    WHERE
      revision.rev_timestamp >= 20220101000000 -- 统计开始时间
      AND revision.rev_timestamp < 20230101000000 -- 统计结束时间
  )
  SELECT
    page_title,
    COUNT(*) AS editors
  FROM
    page_and_editors
  GROUP BY
    page_title
)
SELECT
  CONCAT(
    '* [[:',
    REPLACE(page_title, '_', ' '),
    ']] <small>[',
    editors,
    ']</small>'
  ) AS result
FROM
  pages_vg_revision
ORDER BY
  editors DESC,
  page_title ASC;
SQL

Checking query status...
