Fork of ruwiki templates/modules protect lvl count by Iniquity
This query is marked as a draft This query has been published by Megitsune-chan.

SQL

​x
 
WITH protections_agg AS (
    SELECT 
        pr.pr_page AS page_id,
        GROUP_CONCAT(DISTINCT CONCAT(pr.pr_type, ':', pr.pr_level)) AS protections,
        MAX(
            CASE 
                WHEN pr.pr_expiry IS NULL THEN 'infinity'
                ELSE pr.pr_expiry 
            END
        ) AS expiry_time
    FROM page_restrictions pr
    WHERE 
        pr.pr_expiry IS NULL 
        OR pr.pr_expiry = 'infinity' 
        OR pr.pr_expiry > NOW()
    GROUP BY pr.pr_page
)
​
SELECT 
    CASE 
        WHEN p.page_namespace = 10 THEN 'Шаблон'
        WHEN p.page_namespace = 828 THEN 'Модуль'
        ELSE 'Другое' 
    END AS entity_type,
    p.page_title AS entity_name,
    (
        SELECT COUNT(*)
        FROM templatelinks tl
        INNER JOIN linktarget lt 
            ON tl.tl_target_id = lt.lt_id
        WHERE 
            lt.lt_title = p.page_title 
            AND lt.lt_namespace = p.page_namespace
    ) AS usage_count,
    pa.protections,
    pa.expiry_time
FROM page p
LEFT JOIN protections_agg pa 
    ON p.page_id = pa.page_id
WHERE 
    p.page_namespace IN (10, 828)  -- Шаблоны (10) и модули (828)
    AND pa.protections IS NULL     -- Фильтр по уровню защиты
    AND (
        -- Фильтр по количеству включений
        (
            SELECT COUNT(*)
            FROM templatelinks tl
            INNER JOIN linktarget lt 
                ON tl.tl_target_id = lt.lt_id
            WHERE 
                lt.lt_title = p.page_title 
                AND lt.lt_namespace = p.page_namespace
        ) BETWEEN 250 AND 2000
    )
ORDER BY usage_count DESC;          -- Изначальная сортировка
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...