Users without autoreview right on plwiki by last edit 2

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

#Resultset_1
USE plwiki_p;
SELECT CONCAT("# [[Dyskusja:",p1.page_title,"]]") AS page_title
FROM page AS p1
WHERE p1.page_title NOT LIKE '%/%'
AND p1.page_namespace = 1
AND page_title NOT LIKE 'Bazylika_św._Heleny_w_Birkirkara'
AND page_title NOT LIKE 'Wieża_Delimara'
AND page_title NOT LIKE 'Gaspare_Visconti'
AND page_title NOT LIKE 'Wieża_Bubaqra'
AND page_title NOT LIKE 'Wieża_Cavalier'
AND page_title NOT LIKE 'Wieża_św._Cecylii'
AND NOT EXISTS (SELECT 1
FROM page AS p2
WHERE p2.page_namespace = 0
AND p1.page_title = p2.page_title)
ORDER BY page_title;
#Resultset_2
USE plwiki_p;
SELECT  CONCAT('# [[Dyskusja:',page_title,']]') AS page_title
FROM page, templatelinks
WHERE tl_from_namespace = 1
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title LIKE 'Bez_infoboksu'
AND page_title IN (
	SELECT page_title
	FROM page, templatelinks
	WHERE tl_from_namespace = 0
	AND tl_namespace = 10
	AND page_id = tl_from
	AND tl_title LIKE '%infobox%'
)
ORDER BY page_len, page_title;
#Resultset_3
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cl_to,'|Kategoria:',cl_to,']]') AS cl_to
FROM page, categorylinks
WHERE page_id = cl_from
AND page_namespace = 0
AND cl_to NOT LIKE 'Polacy_odznaczeni_francuskim_Krzyżem_Wojennym'
AND cl_to NOT LIKE 'Majorzy_II_Rzeczypospolitej'
AND cl_to NOT LIKE 'Szemborowo'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE cl_to = p1.page_title
AND p1.page_namespace = 14)
GROUP BY cl_to
ORDER BY cl_to;
#Resultset_4
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[',rd_title,']]') AS rd_title
FROM page
JOIN redirect
ON rd_from = page_id
AND NOT EXISTS (SELECT * FROM page AS p1 WHERE p1.page_title = rd_title AND p1.page_namespace = rd_namespace)
AND page_namespace = 0
AND rd_namespace = 0
AND page_title NOT LIKE 'Club_Deportes_Trasandino'
AND page_title NOT LIKE 'Club_Deportivo_Santamarina'
AND page_title NOT LIKE 'Naval_Talcahuano'
AND page_title NOT LIKE 'Club_Deportivo_y_Social_Naval'
AND page_title NOT LIKE 'Teodorshof'
AND page_title NOT LIKE 'Palazzo_Bubaqra'
AND page_title NOT LIKE 'Wieża_św._Lucjana'
AND page_title NOT LIKE 'Wieża_św._Lukiana'
AND page_title NOT LIKE 'Cavalier_Tower'
AND page_title NOT LIKE 'Wieża_Św._Jerzego'
ORDER BY page_title, rd_title;
#Resultset_5
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title, CONCAT("[[",pl_title,"]]") AS pl_title
FROM pagelinks, page
WHERE pl_title NOT LIKE '%(%)%'
AND pl_title NOT LIKE '%:)%'
AND pl_title NOT LIKE '%:-)%'
AND pl_title NOT LIKE '%)))%'
AND pl_title NOT LIKE '%Pre)Thing%'
AND pl_from = page_id
AND pl_from_namespace = 0
AND pl_namespace = 0
AND (pl_title LIKE '%)%)%' OR pl_title LIKE '%(%(%' OR pl_title LIKE '%(%' OR pl_title LIKE '%)%')
#GROUP BY pl_title
ORDER BY page_title, pl_title;
#Resultset_6
USE plwiki_p;
SELECT CONCAT('# [[User:',page_title,']]') AS page_title
FROM page, templatelinks
WHERE tl_from_namespace = 2
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title LIKE 'DNU'
AND page_title NOT LIKE '%Pablo000%'
GROUP BY page_title
ORDER BY page_title;
#Resultset_7
USE plwiki_p;
SELECT DISTINCT CONCAT("# [[Wikipedia:" , page_title , ']]') as page_title
FROM page
JOIN templatelinks ON page_id = tl_from
JOIN categorylinks ON page_id = cl_from
WHERE page_title LIKE 'Poczekalnia/%'
AND page_title NOT LIKE 'Poczekalnia/Załatwione%'
AND page_title NOT LIKE 'Poczekalnia/Zgłoszenia%'
AND page_title NOT LIKE 'Poczekalnia/preload%'
AND page_title NOT LIKE 'Poczekalnia/wstęp'
AND page_title NOT LIKE 'Poczekalnia/archiwa'
AND page_title NOT LIKE 'Poczekalnia/intro'
AND page_title NOT LIKE 'Poczekalnia/podstrona_załatwione'
AND page_title NOT LIKE 'Poczekalnia/Dyskusja_nad_szablonami'
AND page_title NOT LIKE 'Poczekalnia/Kategorie'
AND page_title NOT LIKE 'Poczekalnia/Szablony'
AND page_title NOT LIKE 'Poczekalnia/schowek_na_postacie_fikcyjne'
AND page_title NOT LIKE 'Poczekalnia/naprawa_załatwione_24'
AND page_title NOT LIKE 'Poczekalnia/naprawa'
AND page_namespace = 4
AND NOT EXISTS(
	SELECT * FROM templatelinks t1 WHERE page_title = t1.tl_title #AND t1.tl_from_namespace = 4
	#LIMIT 1
)
ORDER BY page_title;
#Resultset_8
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title
FROM page
WHERE page_namespace = 0 AND page_len = 0
ORDER BY page_title;
#Resultset_9
USE plwiki_p;
SELECT CONCAT('# [https://pl.wikipedia.org/w/index.php?title=Specjalna%3ALinkujące&hidelinks=1&target=Szablon:',tl_title,'&namespace=0 ',tl_title,']') AS tl_title
FROM page, templatelinks
WHERE tl_from_namespace = 0
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title NOT LIKE 'Toc'
AND tl_title NOT LIKE 'Tc'
AND tl_title NOT LIKE 'Francuska_okupacja_Malty'
AND tl_title NOT LIKE 'Mapa_dane_Polska'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE p1.page_namespace = tl_namespace
AND p1.page_title = tl_title)
GROUP BY tl_title
ORDER BY tl_title;
#Resultset_10
USE plwiki_p;
SELECT concat("# [[Dyskusja:",page_title,"]]") AS page_title
FROM plwiki_p.page
WHERE page_namespace = 1 AND page_len = 0
ORDER BY page_title;
#Resultset_11
USE plwiki_p;
SELECT distinct CONCAT('# [[:Kategoria:',page_title,']]') AS page_title
FROM categorylinks#, page, revision
RIGHT JOIN page
ON cl_to = page_title
JOIN revision
ON rev_page = page_id
WHERE page_namespace = 14
#AND page_is_redirect = 1
AND rev_parent_id = 0
AND cl_to IS NULL
AND page_title NOT LIKE '?'
AND page_title NOT LIKE '%infobo%'
AND page_title NOT LIKE '%artykuł%'
AND page_title NOT LIKE '%Artykuł%'
AND page_title NOT LIKE '%Dyskusj%'
AND page_title NOT LIKE '%Stron%'
AND page_title NOT LIKE '%Hasł%'
AND page_title NOT LIKE '%Literatura_uzupełniająca%'
AND page_title NOT LIKE '%Plik%'
AND page_title NOT LIKE '%nieprawidłowej_przestrzeni%'
AND page_title NOT LIKE '%Moduł%'
AND page_title NOT LIKE '%Szablon%'
AND page_title NOT LIKE '%szablon%'
AND page_title NOT LIKE '%Kategori%'
AND page_title NOT LIKE '%kategori%'
AND page_title NOT LIKE '%Wikiprojekt%'
AND page_title NOT LIKE '%Podano_IUCN%'
AND page_title NOT LIKE '%Wikida%'
AND page_title NOT LIKE '%Wikipedyści%'
AND page_title NOT LIKE '%Parametr%'
AND page_title NOT LIKE '%Linki%'
AND page_title NOT LIKE '%Coor%'
AND page_title NOT LIKE '%Zdjęci%'
AND page_title NOT LIKE '%autorytatywna%'
AND page_title NOT LIKE '%Wikipedii%'
AND page_title NOT LIKE '%PANDA%'
AND page_title NOT LIKE '%NPA%'
AND page_title NOT LIKE '%Ekspresowe_kasowanie%'
AND page_title NOT LIKE '%weryfikacji%'
AND page_title NOT LIKE '%Błęd%'
AND page_title NOT LIKE '%błęd%'
AND page_title NOT LIKE '%Medalowe%'
AND page_title NOT LIKE '%podano%'
AND page_title NOT LIKE '%Ombox%'
AND page_title NOT LIKE '%Pomniejsz%'
AND page_title NOT LIKE '%Przekierowania%'
AND page_title NOT LIKE '%brak%'
AND page_title NOT LIKE '%User%'
AND page_title NOT LIKE '%uzupełnienia%'
AND page_title NOT LIKE '%Przyszłe%'
AND page_title NOT LIKE '%Niezablokowane%'
AND page_title NOT LIKE '%Dorzecze_Tille%'
AND page_title NOT LIKE '%nieznane_parametry%'
AND page_title NOT LIKE '%Głosowania_nad_Listami_na_medal%'
AND page_title NOT LIKE '%wydarzenia_w_sporcie%'
AND page_title NOT LIKE '%wydarzenia_sportowe%'
AND page_title NOT LIKE '%Zdegenerowana_galeria%'
ORDER BY page_title;
#Resultset_12
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[User:',rd_title,']]') AS rd_title
FROM page
JOIN redirect
ON rd_from = page_id
AND page_namespace = 0
AND rd_namespace = 2
ORDER BY page_title, rd_title;
#Resultset_13
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, pp_propname
FROM page_props
JOIN page
ON page_id = pp_page
WHERE page_namespace = 0
AND pp_propname IN ('index', 'newsectionlink', 'noeditsection', 'nonewsectionlink', 'notitleconvert')
AND page_title NOT LIKE '%według_pierwiastków%';
#Resultset_14
use plwiki_p;
select CONCAT("# [[",page_title,"]]") AS page_title, GROUP_CONCAT(cl_to SEPARATOR ',')
from categorylinks
join page on cl_from=page_id
where cl_to rlike "Urodzeni_w_1.*"
and page_title not like '%_i_%'
and page_title not like 'Bracia%'
and page_title not like 'Honor_Blackman'
and page_title not like 'Joyce_Redman'
and page_title not like 'Zhu_Da'
#where cl_to like "%dzimušie" and cl_to not like "%._gadā_dzimušie"
group by cl_from
having count(cl_to)>1;
#Resultset_15
use plwiki_p;
select CONCAT("# [[",page_title,"]]") AS page_title, GROUP_CONCAT(cl_to SEPARATOR ',')
from categorylinks
join page on cl_from=page_id
where cl_to rlike "Zmarli_w_1.*"
and page_title not like '%_i_%'
and page_title not like 'Bracia%'
#where cl_to like "%dzimušie" and cl_to not like "%._gadā_dzimušie"
group by cl_from
having count(cl_to)>1;
#Resultset_16
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cat_title,']]') AS cat_title, cat_pages
FROM page, category
WHERE page_title = cat_title
AND page_namespace = 14
AND page_is_redirect = 1
ORDER BY cat_pages DESC, cat_title ASC;
#Resultset_17
use plwiki_p;
select CONCAT('# [[:Kategoria:',page_title,']]') as page_title
from page
left outer join categorylinks
on cl_from = page_id
where cl_from is null
and page_namespace = 14
and page_title not like 'Kategorie'
and page_title not like 'Wikipedysta:Pablo000/dyskusja';
#Resultset_18
USE plwiki_p;
SELECT DISTINCT CONCAT('# [[',page_title,']]') AS page_title/*, CONCAT('# [[:Plik:',il_to,']]') AS il_to*/
FROM page
JOIN imagelinks
ON page_id = il_from
WHERE (NOT EXISTS(
SELECT 1
FROM image
WHERE img_name = il_to))
AND (NOT EXISTS(
SELECT
1
FROM commonswiki_p.page
WHERE page_title = il_to
AND page_namespace = 6))
AND page_namespace = 0;
#Resultset_19
USE plwiki_p;
SELECT tl_namespace, CONCAT('{{s|',tl_title,'}}') as tl_title, COUNT(*) AS count_tl_title, CONCAT('[https://pl.wikipedia.org/w/index.php?title=Specjalna%3ALinkujące&hidelinks=1&target=Szablon:',tl_title,'&namespace=0 Linkujące]') AS tl_title_url
FROM page, templatelinks
WHERE tl_from_namespace = 0
AND tl_namespace = !10
AND page_id = tl_from
AND tl_title NOT LIKE 'Toc'
AND tl_title NOT LIKE 'Tc'
AND tl_title NOT LIKE 'Lista_planetoid%'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE p1.page_namespace = tl_namespace
AND p1.page_title = tl_title)
GROUP BY tl_title
ORDER BY count_tl_title DESC, tl_title ASC;
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...
