Pivoted Personenkategorien Gender

SQL

/*SELECT * FROM page WHERE page_namespace = 14 AND page_title = 'Person'*/
WITH recursive personenkategorien as (
SELECT p.page_title FROM categorylinks cl
JOIN page p ON p.page_id = cl.cl_from
WHERE cl_type = 'subcat' and cl_to = 'Person'
UNION 
SELECT p.page_title FROM categorylinks cl
JOIN personenkategorien as pk ON pk.page_title = cl.cl_to
JOIN page p ON p.page_id = cl.cl_from
WHERE cl_type = 'subcat'
), people as (
  SELECT cl_from, cl_to as gender FROM categorylinks WHERE cl_to IN ('Mann', 'Frau', 'Nichtbinäre_Person', 'Geschlecht_unbekannt') 
),
genders as (
  SELECT 'Mann' gender FROM dual
UNION ALL 
SELECT 'Frau' FROM dual
UNION ALL
SELECT 'Nichtbinäre_Person' FROM dual
UNION ALL 
SELECT 'Geschlecht_unbekannt' FROM dual
)

SELECT 
	cl.cl_to, 
    count(distinct CASE WHEN p.gender = 'Mann' THEN cl.cl_from END) mann_pages,
    count(distinct CASE WHEN p.gender = 'Frau' THEN cl.cl_from END) frau_pages,
    count(distinct CASE WHEN p.gender = 'Nichtbinäre_Person' THEN cl.cl_from END) nb_pages,
    count(distinct CASE WHEN p.gender = 'Geschlecht_unbekannt' THEN cl.cl_from END) uk_pages
FROM categorylinks cl
JOIN people p ON cl.cl_from = p.cl_from
GROUP BY 1
SQL

Checking query status...
