Testing - Quarry

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

SET @whitelistPage = 62534307;
SET @botActorId = 192619287;
SET @userNamespace = 2;

/* Pages created by whitelisted users and patrolled by bot that don't currently exist */
SELECT
    patrol.rc_timestamp,
    patrol.rc_title,
    creator.actor_name,
    deletionreason.comment_text
FROM
	recentchanges_userindex patrol
JOIN
	logging_logindex creation
	ON
		patrol.rc_namespace = creation.log_namespace
    	AND patrol.rc_title = creation.log_title
    	AND creation.log_type = 'create'
JOIN
	actor_user creator
	ON creation.log_actor = creator.actor_id
JOIN
	(
		SELECT REPLACE(links.pl_title, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_namespace = @userNamespace
	) AS listed
	ON listed.username = creator.actor_name
LEFT JOIN
	page currentPages
	ON
		patrol.rc_namespace = currentPages.page_namespace
    	AND patrol.rc_title = currentPages.page_title
JOIN
	logging_logindex deletion
	ON
		patrol.rc_namespace = deletion.log_namespace
		AND patrol.rc_title = deletion.log_title
		AND deletion.log_type = 'delete'
JOIN
	comment deletionreason
	ON deletionreason.comment_id = deletion.log_comment_id
WHERE patrol.rc_actor = @botActorId
	AND patrol.rc_log_type = 'pagetriage-curation'
	AND currentPages.page_id IS NULL
ORDER BY patrol.rc_timestamp DESC
;
/*
SET @changedRedirectTagId = 10;
SET @newRedirectTagId = 11;
SET @removedRedirectTagId = 25;
/*
SELECT
	page_id AS 'pageid',
	page_title AS 'title',
	ptrpt_value AS 'target',
	actor_name AS 'creator'
FROM
	page
	JOIN pagetriage_page ON page_id = ptrp_page_id
	JOIN pagetriage_page_tags ON ptrp_page_id = ptrpt_page_id
	JOIN revision ON page_latest = rev_id
	JOIN actor ON rev_actor = actor_id
    JOIN centralauth_p.globaluser gu ON gu.gu_name = actor.actor_name
    JOIN centralauth_p.global_user_groups gug ON gug.gug_user = gu.gu_id
WHERE
	ptrp_reviewed = 0
	AND ptrpt_tag_id = 9
	AND page_namespace = 0
	AND page_is_redirect = 1
    AND gug.gug_group = 'global-rollbacker'
;

/*
SELECT pg.page_namespace, pg.page_title, rd.*
FROM page pg
JOIN redirect rd
ON pg.page_id = rd.rd_from
WHERE rd.rd_namespace <> 0
LIMIT 10;
/*
USE metawiki_p;
SELECT ug.ug_group, u.user_name FROM user_groups ug
JOIN user u ON ug.ug_user = u.user_id
JOIN centralauth_p.globaluser gu ON u.user_name = gu.gu_name
WHERE gu.gu_locked = 1
#LIMIT 1;
/*
use frwiki_p;
SELECT page_title #COUNT(*), linter_params
FROM linter
JOIN page ON page_id = linter_page
WHERE linter_cat = 3
AND linter_params = '{"items":[""]}'
AND page_namespace = 0
#AND linter_params NOT LIKE '%templateInfo%'
#GROUP BY linter_params
#ORDER BY COUNT(*) DESC
LIMIT 1000;
/*
USE specieswiki_p;
SELECT CONCAT('Template:', page_title) FROM page
WHERE page_namespace = 10
AND page_is_redirect = 1
AND page_title LIKE 'Zt%'
AND NOT EXISTS (
  SELECT 1
  FROM templatelinks
  WHERE tl_title = page_title
  AND tl_namespace = 10
)
AND NOT EXISTS (
  SELECT 1
  FROM pagelinks
  WHERE pl_title = page_title
  AND pl_namespace = 10
)
LIMIT 100;
/*
SELECT CONCAT('Template:', page_title), SUBSTRING(page_title, 18) FROM page
WHERE page_namespace = 10
AND page_title LIKE 'Editnotices/Page/%'
AND page_title NOT LIKE 'Editnotices/Page/%:%'
AND page_is_redirect = 0
AND SUBSTRING(page_title, 18) NOT IN (
  SELECT page_title
  FROM page
  WHERE page_namespace = 0
)
LIMIT 100;
/*SELECT *
FROM page
LEFT JOIN pagetriage_page ON ptrp_page_id = page_id
LEFT JOIN pagetriage_page_tags ON ptrpt_page_id = ptrp_page_id
WHERE page_id = 62933448
;
/*
USE commonswiki_p;
SELECT page_title FROM page
WHERE page_namespace = 6
AND page_is_redirect = 0
AND page_title LIKE '%..___'
LIMIT 1000
OFFSET 100;

/*
SELECT *
FROM templatelinks
WHERE tl_namespace = 6
AND NOT EXISTS (
  SELECT 1
  FROM imagelinks
  WHERE il_from = tl_from
  AND il_to = tl_title
  LIMIT 1
)
LIMIT 10;

/*
USE wikidatawiki_p;
SELECT pg.page_title AS `Redirect from`, r.rd_title AS `Redirect to` #pg.*, r.*
FROM page pg
JOIN redirect r ON r.rd_from = pg.page_id
WHERE page_is_redirect = 1
AND pg.page_namespace = 0
AND EXISTS (
	SELECT 1
	FROM page pg2
	WHERE pg2.page_title = pg.page_title
	AND pg2.page_namespace = 1
	AND pg2.page_is_redirect = 0
	LIMIT 1
)
AND NOT EXISTS (
	SELECT 1
	FROM page pg3
	WHERE pg3.page_title = r.rd_title
	AND pg3.page_namespace = 1
	LIMIT 1
)
LIMIT 100;
/*
SELECT pp.pp_value #pg.page_id, pl.*, pp.*
FROM pagelinks pl
JOIN page AS pg
ON page_title = pl_title AND page_namespace = pl_namespace
JOIN page_props AS pp
ON pp_page = page_id AND pp_propname = 'wikibase_item'
WHERE pl_from = 39377526
AND EXISTS (
	SELECT 1
	FROM categorylinks
	WHERE cl_from = pg.page_id
	AND cl_to = 'Living_people'
)
*/
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...
