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 *

FROM (

    SELECT 

        p.page_title AS template_name,

        pa.protections,

        (

            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.expiry_time

    FROM page p

    LEFT JOIN protections_agg pa 

        ON p.page_id = pa.page_id

    WHERE 

        p.page_namespace = 10

    GROUP BY 

        p.page_id, pa.protections, pa.expiry_time

) AS subquery

WHERE usage_count > 249  -- Фильтр по количеству включений

ORDER BY usage_count DESC;