use eswiki_p;

​

SELECT date_format(rev_timestamp, '%Y-%m') as mes, sum(isHombre) as hombres_total, sum(isMujer) as mujeres_total

, sum(case when isHombre = 1 and isFutbolista = 0 THEN 1 END) hombres_no_futbol

, sum(case when isHombre = 1 and isFutbolista = 1 THEN 1 END) hombres_futbol

, sum(case when isMujer = 1 and isFutbolista = 0 THEN 1 END) mujeres_no_futbol

, sum(case when isMujer = 1 and isFutbolista = 1 THEN 1 END) mujeres_futbol

FROM 

(select page_id, if(cl_to = 'Hombres', 1, 0) isHombre, if(cl_to = 'Mujeres', 1, 0) isMujer, ifnull((SELECT 1 FROM categorylinks cat2 WHERE page_id = cat2.cl_from and cat2.cl_to  like 'Futbolista%' LIMIT 1), 0) isFutbolista,

    rev_timestamp

from revision, page, categorylinks cat

WHERE rev_parent_id = 0

    AND page_id = rev_page AND page_namespace = 0

    AND cl_from = page_id

    AND cl_to in ('Hombres', 'Mujeres')

    AND year(rev_timestamp) >= 2018

    ) AS a

GROUP BY 1

ORDER BY 1;

​

select page_id, page_title, if(cl_to = 'Hombres', 1, 0) isHombre, if(cl_to = 'Mujeres', 1, 0) isMujer

, ifnull((SELECT 1 FROM categorylinks cat2 WHERE page_id = cat2.cl_from and cat2.cl_to  like 'Futbolista%' LIMIT 1), 0) isFutbolista,

    rev_timestamp

from revision, page, categorylinks cat

WHERE rev_parent_id = 0

    AND page_id = rev_page AND page_namespace = 0

    AND cl_from = page_id

    AND cl_to in ('Hombres', 'Mujeres')

    AND year(rev_timestamp) >= 2018