CSD G8 subpages watchlist

SQL

with recursive parent_index (child, parent, occur) as (
  select page_title, substring_index(page_title, '/', length(page_title) - length(replace(page_title, '/', ''))),
  length(page_title) - length(replace(page_title, '/', '')) from page
  where page_namespace = 10 and replace(page_title, '/', '') != page_title
  union distinct
  select parent, substring_index(parent, '/', occur - 1), occur - 1 from parent_index where occur > 1
),
search as (
  select distinct parent as candidate from parent_index where occur = 1
  and parent not in (select page_title from page where page_namespace = 10)
  union distinct
  select child from search join parent_index on candidate = parent
  where candidate not in (select page_title from page where page_namespace = 10)
)

select page_title from page join search on page_title = candidate
where page_namespace = 10 and page_is_redirect = 0 and page_title not like '%/TDs'
limit 100
SQL

Checking query status...
