ltwiki stats of list of articles every Wikipedia should have

Fork of Fiwiki stats of list of articles every Wikipedia should have by Zache

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

SQL

use metawiki_p;
SELECT 
  concat("[[", replace(page_title, "_", " "), "]]") as page_title, 
  page_len, 
  concat("{{Q|", pp_value, "}}") as wikidata_item,
  group_concat(DISTINCT(IFNULL(concat("[[:category:", replace(cl_to, "_", " "), "|]]"),"")) separator ", " ) as categories
FROM 
  metawiki_p.iwlinks, 
  ltwiki_p.page_props,
  ltwiki_p.page 
  LEFT JOIN (select cl2.cl_from, page_title as cl_to from ltwiki_p.page, ltwiki_p.categorylinks as cl1, fiwiki_p.categorylinks as cl2 where cl1.cl_to="Huomiota_kaipaavat_sivut" and page_id=cl1.cl_from and cl2.cl_to=page_title) as problemcats ON cl_from=page_id
WHERE 
  iwl_prefix="d" 
  AND iwl_from=7066 
  AND pp_propname="wikibase_item" 
  AND iwl_title=pp_value 
  AND pp_page=page_id
GROUP BY page_id
ORDER BY page_title

SQL

Checking query status...