CSD G8 subpages watchlist

SQL

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

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

Checking query status...
