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

SQL

use
zhwiki_p;

WITH pages_with_editing AS (SELECT rev_page
                            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_target_id = (SELECT lt_id
                                                                      FROM linktarget
                                                                      WHERE lt_namespace = 10 AND lt_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 pages_vg_talk.page_namespace = 1 AND
                                                 page_main.page_title = pages_vg_talk.page_title AND
                                                 page_main.page_namespace + 1 = pages_vg_talk.page_namespace),

     page_edit_count AS (SELECT page_name, COUNT(*) AS edits
                         FROM pages_with_editing,
                              pages_vg
                         WHERE pages_with_editing.rev_page = pages_vg.page_id)

SELECT concat('* [[:', page_edit_count.page_name, ']] ', '<small>[', page_edit_count.edits, ']</small>') AS result
FROM page_edit_count
WHERE page_edit_count.edits >= 5
ORDER BY page_edit_count.page_name DESC, page_edit_count.edits ASC;
SQL

Checking query status...
