user categories tier 1

SQL

USE enwiki_p;

with user_cat_page as (
	select cl_from, cl_to, page.page_id, page.page_namespace
  	from categorylinks
	join page on page.page_title = categorylinks.cl_to
    where page.page_namespace = 14
  	-- and cl_to = "Wikipedians_by_hobby"
  limit 1000
)
select 
cl_from as child_cat_page_id, 
cl_to as cat_title,
user_cat_page.page_id as cat_page_id,
page.page_title as child_cat_title
from user_cat_page 
left join page on page.page_id = cl_from
where page.page_namespace = 14


/*
select cl_from as parent_page_id, cl_to as user_cat_title, cl_timestamp, page.page_title as parent_page_title
from categorylinks 
join page on page.page_id = categorylinks.cl_from
where page.page_namespace = 14
limit 10
*/
SQL

Checking query status...
