Wikipedia:Database reports/Unused templates (filtered), w/o 2ary queries

From https://github.com/mzmcbride/database-reports/blob/main/dbreps2/src/enwiki/unusedtemplatesfiltered.rs

Includes templates that are transcluded only by themselves.
(Also tightens up the exclusions, by escaping underscores in the LIKE clauses.)
(Also eliminates the need for all those secondary queries.)

For [[Wikipedia talk:Database reports#Wikipedia:Database reports/Unused templates (filtered) update related to Module:Pagetype]] circa 22 February 2024.

SQL

SELECT
  ut_title,
  MIN(rev_timestamp),
  MAX(rev_timestamp),
  COUNT(DISTINCT rev_actor),
  COUNT(rev_id)
FROM
  (
    SELECT
      page_id AS ut_id,
      page_title AS ut_title
    FROM
      page
    LEFT JOIN linktarget ON page_namespace = lt_namespace
      AND page_title = lt_title
    LEFT JOIN templatelinks ON tl_target_id=lt_id AND tl_from <> page_id
    WHERE
      page_namespace = 10
      AND page_is_redirect = 0
      AND tl_target_id IS NULL
      AND page_title NOT LIKE "Adminstats/%"
      AND page_title NOT LIKE "AfC\_%"
      AND page_title NOT LIKE "Cite\_doi/%"
      AND page_title NOT LIKE "Cite\_pmid/%"
      AND page_title NOT LIKE "Did\_you\_know\_nominations/%"
      AND page_title NOT LIKE "Editnotices/%"
      AND page_title NOT LIKE "PBB/%"
      AND page_title NOT LIKE "POTD\_caption/%"
      AND page_title NOT LIKE "POTD\_credit/%"
      AND page_title NOT LIKE "POTD\_protected/%"
      AND page_title NOT LIKE "TemplateStyles\_sandbox/%"
      AND page_title NOT LIKE "TFA\_title/%"
      AND page_title NOT LIKE "User\_%"
      AND page_title NOT LIKE "%/testcases"
      AND page_title NOT LIKE "%/sandbox"
      AND page_title NOT LIKE "%/rater-data.js"
      AND page_title NOT LIKE "%-stub"
      AND page_title NOT IN (
        SELECT
          page_title
        FROM
          page
          JOIN categorylinks ON page_id = cl_from
        WHERE
          cl_to IN (
            'Wikipedia_substituted_templates',
            'Wikipedia_transclusionless_templates',
            'Deprecated_templates_kept_for_historical_reasons',
            'Inactive_project_pages',
            'Parameter_shared_content_templates',
            'Computer_language_user_templates',
            'Language_user_templates',
            'Template_test_cases',
            'Template_sandboxes',
            'Level-zero_userbox_templates',
            'Templates_for_deletion'
          )
          AND page_namespace = 10
      )
    ORDER BY
      page_title ASC
    ) ut
  STRAIGHT_JOIN revision ON rev_page = ut_id
GROUP BY ut_title;

SQL

Checking query status...