CSD G8 subpages watchlist

SQL

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

select count(*) from occur_index join search on page_title = candidate where page_is_redirect = 0
SQL

Checking query status...
