Recursion on categorylinks

SQL

with recursive subcats (depth, cat_title, cat_title_join) as (
  select distinct 0, cl_to, cl_to from categorylinks where cl_to in ('Lists')
  union all
  select depth + 1, page_title, ROW_NUMBER() OVER (PARTITION BY cat_title ORDER BY cat_title) from subcats
  join categorylinks on cl_to = cat_title and cl_type = 'subcat'
  join page on cl_from = page_id
  where depth < 3
)

-- select count(cat_title), count(distinct 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 * from subcats limit 1;

/*
select count(distinct page_namespace, page_title) from subcats
join categorylinks on cl_to = cat_title and cl_type != 'subcat'
join page on cl_from = page_id
order by page_namespace;
*/
SQL

Checking query status...
