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

SQL

x
 
-- set max_recursive_iterations=3;
with recursive subcats (cat_title) as (
  select cl_to from categorylinks where cl_to in ('Lists')
  -- union all
  union distinct
  select page_title from subcats
  join categorylinks on cl_to = cat_title and cl_type = 'subcat'
  join page on cl_from = page_id
)
-- cycle cat_title restrict
-- select count(cat_title) from subcats;
-- select min(depth), max(depth), cat_title, count(*) from subcats group by cat_title order by count(depth) desc limit 10;
/*
select count(distinct cl_from) from subcats
join categorylinks on cl_to = cat_title and cl_type != 'subcat'
-- where cat_title not in (select cat_title from category where cat_pages = 0)
-- join page on cl_from = page_id
-- order by page_namespace;
*/
select count(distinct page_id) from subcats
join categorylinks on cl_to = cat_title and cl_type = 'page'
join page on cl_from = page_id and page_namespace = 0
/*
SELECT p1.page_title, pl_title, COUNT(pl_title) as links
FROM page p1 LEFT JOIN pagelinks ON pl_from = p1.page_id AND pl_namespace = 0
AND pl_title IN (select p2.page_title from page p2 where p2.page_namespace = 0)
-- LEFT JOIN page p2 ON p2.page_title = pl_title AND p2.page_namespace = 0
-- join categorylinks on cl_from = p1.page_id and cl_to in ("All_disambiguation_pages", "All_set_index_articles")
join categorylinks on cl_type != 'subcat' and cl_from = pl_from
join subcats on cl_to = cat_title
-- WHERE p1.page_id IN (select cl_from from categorylinks where cl_to in ("All_disambiguation_pages", "All_set_index_articles"))
WHERE p1.page_namespace = 0 AND p1.page_is_redirect = 0
GROUP BY p1.page_title
HAVING p1.page_title LIKE '%\_(disambiguation)' AND links < 2 OR links < 1
ORDER BY links
*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...