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
)

recursive parent_index (parent, child, occur) as (
  select occur_index.page_title, substring_index(occur_index.page_title, '/', occur_index.occur), occur_index.occur from occur_index
  union distinct
  select parent_index.child, substring_index(parent_index.child, '/', parent_index.occur - 1), parent_index.occur - 1 from parent_index where parent_index.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...
