!temp to pages

SQL

WITH InAr AS (
  SELECT ll_from FROM langlinks WHERE ll_lang = 'ar'
)
SELECT CONCAT('[[:en:Template:', page_title,"]]") as "en",
CONCAT('[[', page_title,"]]") as "!",
CONCAT('[[:Template:', page_title,"]]") as "ar",
 count(pl_target_id)
 #SUM(CASE WHEN pl_target_id IN (SELECT ll_from FROM InAr) THEN 1 ELSE 0 END) AS "cd",
FROM page
JOIN pagelinks
ON pl_from = page_id
WHERE page_namespace = 10

AND pl_target_id IN (SELECT ll_from FROM InAr)

AND pl_target_id IN (SELECT p.page_id FROM page p WHERE p.page_id = pl_target_id and p.page_namespace = 0)

AND page_id NOT IN (SELECT ll_from FROM langlinks WHERE ll_lang = 'ar' AND ll_from = page_id)

and page_id in (
            SELECT tl.tl_from FROM templatelinks tl, linktarget lt
            WHERE lt.lt_namespace = 10
            and tl.tl_target_id = lt.lt_id
            and lt.lt_title = "Navbox"
        )
And page_title not like "%/%"
group by page_title
order by COUNT(pl_target_id) desc

Limit 3
;
SQL

Checking query status...
