Redirected portals with existing subpages

SQL

select p1.page_title, rd_title, count(p2.page_title) as subpages, count(p3.page_title) from page p1
join redirect on rd_namespace = 100 and rd_from = p1.page_id
join page p2 on concat(p1.page_title, '/') = substring(p2.page_title, 1, length(p1.page_title)+1)
and p2.page_namespace = 100 and p2.page_is_redirect = 0
join page p3 on concat(p1.page_title, '/') = substring(p3.page_title, 1, length(p1.page_title)+1)
and p3.page_namespace = 101 and p3.page_is_redirect = 0
where p1.page_namespace = 100 and p1.page_id in (
  select cl_from from categorylinks
  where cl_to in ("Redirected_portals_with_existing_subpages") and cl_type = 'page'
)
group by p1.page_title order by subpages desc
SQL

Checking query status...
