Most used autotranslate non-Translate templates on Commons

Fork of Most used autotranslate templates on Commons by Nemo bis

This query is marked as a draft This query has been published by Nemo bis.

SQL

USE commonswiki_p;
SELECT tl_title, COUNT(*)
FROM templatelinks
WHERE tl_namespace = 10
AND tl_title IN (
  SELECT page_title
  FROM page
  JOIN categorylinks
  ON page_id = cl_from
  AND page_namespace = 10
  AND cl_to = 'Autotranslated_templates'
  ORDER BY tl_title
)
AND tl_title NOT IN (
  SELECT page_title
  FROM page
  JOIN categorylinks
  ON page_id = cl_from
  AND page_namespace = 10
  AND cl_to = 'Templates_using_the_Translate_extension_for_internationalisation'
  OR cl_to = 'Templates_using_MediaWiki_messages_from_translatewiki.net_for_internationalisation'
  ORDER BY tl_title
)
GROUP BY tl_title
ORDER BY COUNT(*) DESC
LIMIT 1000;

SQL

Checking query status...