编辑人数最多的电子游戏条目 (zhwiki)

SQL

WITH pages_with_editing AS (SELECT DISTINCT rev_page, rev_actor
                            FROM revision
                            WHERE rev_timestamp >= 20220101000000 -- Start time
                              AND rev_timestamp < 20230101000000 -- End time
),
     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),
     pages_vg AS (SELECT page_main.page_id, page_main.page_title
                  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),
     pages_edited_by_different_editors AS (SELECT pages_vg.page_title, COUNT(*) AS editors
                                           FROM pages_with_editing
                                                    JOIN pages_vg ON pages_with_editing.rev_page = pages_vg.page_id
                                           GROUP BY pages_vg.page_id)

SELECT CONCAT('* [[', page_title, ']]', ' <small>[', editors, ']</small>') AS result
FROM pages_edited_by_different_editors
WHERE editors >= 5
ORDER BY editors DESC, page_title ASC;
SQL

Checking query status...
