Categories who are under hidden categories only (zhwiki_p)

SQL

WITH hc AS (
  	-- list of pages under category:隐藏分类
    SELECT p.page_id, p.page_title
	FROM categorylinks c
	LEFT JOIN page p ON (c.cl_from = p.page_id)
  	WHERE c.cl_to = "隐藏分类"
), hc2 AS (
	SELECT p.page_title, pp.pp_propname
	FROM page_props pp 
	LEFT JOIN page p ON (p.page_id = pp.pp_page)
	WHERE pp.pp_propname = 'hiddencat'
)
-- list of categories under each page
SELECT hc.page_id, hc.page_title, c.cat_pages, SUM(CASE WHEN hc2.pp_propname IS NOT NULL THEN 1 ELSE 0 END) AS hidden_cat_count, 
	COUNT(*) AS cat_count
FROM hc
LEFT JOIN categorylinks c2 ON (hc.page_id = c2.cl_from)
LEFT JOIN category c on (c.cat_title = hc.page_title)
LEFT JOIN hc2 on (c.cat_title = p.page_title)
GROUP BY 1, 2, 3
HAVING COUNT(*) = SUM(CASE WHEN hc2.pp_propname IS NOT NULL THEN 1 ELSE 0 END)
ORDER BY 3 DESC
LIMIT 10
SQL

Checking query status...
