use fiwiki_p;

​

​

select 

    concat("# [[", p1.page_title, "]] ") as page_title,

    concat("<small>", group_concat(distinct(IF(cl2.cl_from IS NOT NULL, concat("[[:Luokka:", cl1.cl_to,"|", cl1.cl_to,"]], "), NULL))), "</small>") as cl_to,

    count(distinct(r1.rev_user)) as u

from 

    user, 

    logging_compat as l1 

    LEFT JOIN page as p1 ON l1.log_page=p1.page_id AND p1.page_namespace=0 

    LEFT JOIN change_tag ON ct_log_id=log_id 

    LEFT JOIN change_tag_def ON ct_tag_id=ctd_id AND ctd_name = "contenttranslation" 

    LEFT JOIN categorylinks as cl1 ON cl1.cl_from=page_id

    LEFT JOIN page as p2 ON cl1.cl_to=p2.page_title AND p2.page_namespace=14 AND cl_to NOT IN (

      "Small-elementtiä_käyttämättömät_tarkenteelliset_tietolaatikot",

      "Käännetyt_artikkelit",

      "Tietolaatikon_nimi-parametri_ei_vastaa_Wikidatassa_olevaa_nimeä"

    )  

    LEFT JOIN categorylinks as cl2 ON cl2.cl_from=p2.page_id AND cl2.cl_to="Huomiota_kaipaavat_sivut"

    LEFT JOIN revision_compat as r1 ON p1.page_id=r1.rev_page AND r1.rev_id>=ct_rev_id

where 

    user_registration > 20200014210707 

    and user_id=l1.log_user 

    and l1.log_type="create" 

    and p1.page_id IS NOT NULL

    and ctd_id IS NOT NULL

    and l1.log_namespace=0 and log_id>11602240 

group by p1.page_id

​