Blank - Quarry

This query is marked as a draft This query has been published by Edgars2007.
SQL

/*USE lvwiki_p;
SELECT p.page_title#, COUNT(pl.pl_from)
#SELECT p.page_title
FROM page p
left JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
left JOIN page p2 ON p2.page_id = pl.pl_from
AND pl.pl_from_namespace = 0 and p2.page_is_redirect=0
and p.page_id not in (select c.cl_from from categorylinks c where c.cl_to = "Izolētie_raksti")
and pl.pl_from is null
GROUP BY p.page_title
#having COUNT(pl.pl_from)<1
ORDER BY COUNT(pl.pl_from) DESC




use lvwiki_p;
select pl.pl_title, COUNT(pl.pl_title)
from pagelinks pl
#left join page p on pl.pl_from=p.page_id
where #pl.pl_title="1951._gada_Tour_de_France" and
NOT EXISTS (Select sp1.page_title from page sp1 where pl.pl_title=sp1.page_title and sp1.page_namespace=0) and pl.pl_from_namespace=0 and pl.pl_namespace=0
and NOT EXISTS (select spl1.pl_title from pagelinks spl1 where pl.pl_title=spl1.pl_title and spl1.pl_from=184158) and
#and p.page_id NOT EXISTS (select tl1.tl_from from templatelinks tl1 where tl1.tl_title="Navbox" and tl1.tl_from_namespace=10)
 NOT EXISTS 
(
select spl2.pl_title
from pagelinks spl2
left join page sp3 on spl2.pl_from=sp3.page_id
where #pl.pl_title="1951._gada_Tour_de_France" and
pl.pl_title=spl2.pl_title and sp3.page_namespace=10 and  EXISTS (select tl2.tl_from
from templatelinks tl2
where
#(tl2.tl_title="Navbox" or tl2.tl_title="Sidebar") and
spl2.pl_from=tl2.tl_from and tl2.tl_from_namespace=10)
and spl2.pl_namespace=0
)
	#and pl.pl_title NOT LIKE "%Tour_de_France"
group by pl.pl_title
having COUNT(pl.pl_title)>10
order by COUNT(pl.pl_title) DESC
limit 15;

use lvwiki_p;
select pl.pl_title, COUNT(pl.pl_title)
from pagelinks pl
where NOT EXISTS (Select sp1.page_title from page sp1 where pl.pl_title=sp1.page_title and sp1.page_namespace=0)
and pl.pl_from_namespace=0 and pl.pl_namespace=0
and NOT EXISTS (select spl1.pl_title from pagelinks spl1 where pl.pl_title=spl1.pl_title and spl1.pl_from=184158)
and NOT EXISTS (select spl2.pl_title
					from pagelinks spl2
					left join page sp3 on spl2.pl_from=sp3.page_id
					where pl.pl_title=spl2.pl_title and sp3.page_namespace=10
                			and EXISTS (select tl2.tl_from
										from templatelinks tl2
										where spl2.pl_from=tl2.tl_from and tl2.tl_from_namespace=10)
							and spl2.pl_namespace=0)
group by pl.pl_title
having COUNT(pl.pl_title)>10
order by COUNT(pl.pl_title) DESC
limit 15;



use enwiki_p;
select pl.pl_title, COUNT(pl.pl_title)
from pagelinks pl
where pl.pl_from_namespace=0 and pl.pl_namespace=0
and exists (select pla.page_title
		from categorylinks cla
		join page pla on cla.cl_from=pla.page_id
		where pl.pl_from=pla.page_id and pla.page_namespace=1 and cla.cl_type="page" and cla.cl_to="WikiProject_Linguistics_articles")
		and NOT EXISTS (Select sp1.page_title from page sp1 where pl.pl_title=sp1.page_title and sp1.page_namespace=0)
and NOT EXISTS (select spl2.pl_title
					from pagelinks spl2
					left join page sp3 on spl2.pl_from=sp3.page_id
					where pl.pl_title=spl2.pl_title and sp3.page_namespace=10
                			and EXISTS (select tl2.tl_from
										from templatelinks tl2
										where spl2.pl_from=tl2.tl_from and tl2.tl_from_namespace=10)
							and spl2.pl_namespace=0)
group by pl.pl_title
#having COUNT(pl.pl_title)>10
order by COUNT(pl.pl_title) DESC
limit 1000;


use enwiki_p;
select pl.pl_title, COUNT(pl.pl_title)
from pagelinks pl
where pl.pl_from_namespace=0 and pl.pl_namespace=0
and exists (select pla.page_title
		from categorylinks cla
		join page pla on cla.cl_from=pla.page_id
		where pl.pl_from=pla.page_id and pla.page_namespace=1 and cla.cl_type="page" and cla.cl_to="WikiProject_Linguistics_articles")
		and NOT EXISTS (Select * from page sp1 where pl.pl_title=sp1.page_title and sp1.page_namespace=0)
and NOT EXISTS (select *
					from pagelinks spl2
					left join page sp3 on spl2.pl_from=sp3.page_id
					where pl.pl_title=spl2.pl_title and sp3.page_namespace=10
                			and EXISTS (select *
										from templatelinks tl2
										where spl2.pl_from=tl2.tl_from and tl2.tl_from_namespace=10)
							and spl2.pl_namespace=0)
group by pl.pl_title
#having COUNT(pl.pl_title)>10
order by COUNT(pl.pl_title) DESC
limit 1000;
*/
use eijnwiki_p;
select pl.pl_title, COUNT(pl.pl_title)
from pagelinks pl
where pl.pl_from_namespace=0 and pl.pl_namespace=0
and exists (select *
		from categorylinks cla
		join page pla on cla.cl_from=pla.page_id
		where pla.page_namespace=1 and cla.cl_type="page" and cla.cl_to="WikiProject_Linguistics_articles")
and NOT EXISTS (Select * from page sp1 where pl.pl_title=sp1.page_title and sp1.page_namespace=0)
and NOT EXISTS (select *
					from pagelinks spl2
					left join page sp3 on spl2.pl_from=sp3.page_id
					where pl.pl_title=spl2.pl_title and sp3.page_namespace=10
                			and EXISTS (select *
										from templatelinks tl2
										where spl2.pl_from=tl2.tl_from and tl2.tl_from_namespace=10)
							and spl2.pl_namespace=0)
group by pl.pl_title
#having COUNT(pl.pl_title)>10
order by COUNT(pl.pl_title) DESC
limit 1000;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
