CSD G8 subpages watchlist

SQL

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

with recursive parent_index (parent, child, occur) as (
  select page_title, substring_index(page_title, '/', occur), occur from occur_index
  union distinct
  select child, substring_index(child, '/', occur - 1), occur - 1 from parent_index where occur > 1
)

/*
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(*) from parent_index

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

Checking query status...
