编辑人数最多的电子游戏条目 (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...
