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)
)

/*
with occur as (
  select page_title, length(page_title) - length(replace(page_title, '/', '')) as occurrence from page
  where page_namespace = 10
)

select occur.page_title, occur.occurrence, subpage.page_title from occur sub
join occur sur on substring(occur.page_title, 1, length(subpage.page_title)) = subpage.page_title
where subpage.occurrence < occur.occurrence
*/

select count(distinct candidate) from search
where candidate in (select page_title from page where page_namespace = 10)

-- select page_title, base_name, occurrence, min(occurrence) from occur group by base_name having min(occurrence) > 1;
SQL

Checking query status...
