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