This query is marked as a draft This query has been published by Matěj Suchánek.

SQL

x
 
/*
USE wikidatawiki_p;
SELECT wbit_item_id, wbxl_language, wbx_text
FROM wbt_item_terms
INNER JOIN wbt_term_in_lang ON wbit_term_in_lang_id = wbtl_id
INNER JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id
INNER JOIN wbt_text ON wbxl_text_id = wbx_id
WHERE wbtl_type_id = 2 AND LENGTH(wbx_text) > 250
LIMIT 100;
USE wikidatawiki_p;
SELECT page_title FROM page
WHERE page_namespace = 0 AND page_is_redirect = 0 AND NOT EXISTS (
  SELECT 1 FROM wbt_item_terms JOIN wbt_term_in_lang ON wbtl_id = wbit_term_in_lang_id
  WHERE wbtl_type_id IN (SELECT wby_id FROM wbt_type WHERE wby_name = 'label')
  AND wbit_item_id = REPLACE(page_title, 'Q', '')
)
LIMIT 10;
USE wikidatawiki_p;
SELECT COUNT(*) FROM revision_userindex
JOIN actor_revision ON actor_id = rev_actor
JOIN comment_revision ON comment_id = rev_comment_id
WHERE actor_name = 'MatSuBot' AND comment_text LIKE '%missing labels%'
#AND rev_timestamp BETWEEN '20200713' AND '20200718' 24632
#AND rev_timestamp BETWEEN '20200706' AND '20200711' 55919
#AND rev_timestamp BETWEEN '20200629' AND '20200704' 63166
#AND rev_timestamp BETWEEN '20200622' AND '20200626' 50006
#AND rev_timestamp BETWEEN '20200615' AND '20200619' 43826
#AND rev_timestamp BETWEEN '20200608' AND '20200612' 62733
#AND rev_timestamp BETWEEN '20200601' AND '20200605' 17827
#AND rev_timestamp BETWEEN '20200525' AND '20200529' 26238
#AND rev_timestamp BETWEEN '20200518' AND '20200522' 32090
#AND rev_timestamp BETWEEN '20200511' AND '20200515' 1364
AND rev_timestamp >= '2020';
*/
#USE cswiki_p; SELECT * FROM wbc_entity_usage WHERE eu_aspect = 'D.cs';
/*
USE cswiki_p;
SELECT DISTINCT pl_title FROM pagelinks WHERE pl_namespace = 10 AND pl_title LIKE 'Vítejte\_-%';
SELECT pl_from_namespace, COUNT(DISTINCT pl_from) FROM pagelinks
WHERE pl_namespace = 10 AND pl_title LIKE 'Vítejte\_-%'
GROUP BY pl_from_namespace;
USE wikidatawiki_p;
SELECT rc_timestamp, rc_namespace, rc_title, actor_name, comment_text
FROM recentchanges
JOIN comment_recentchanges ON comment_id = rc_comment_id
JOIN actor_recentchanges ON actor_id = rc_actor
WHERE rc_timestamp BETWEEN '2020081409' AND '20200818'
AND rc_namespace IN (0, 120)
AND comment_text LIKE '%User:Frettie/consistency check add.js%'
ORDER BY rc_timestamp DESC;
USE cswiki_p;
SELECT page_namespace, page_title, page_is_redirect FROM page
WHERE page_title RLIKE "[^,.:;[:alnum:]!()\\-'/&]"
AND page_namespace NOT IN (1,2,3,5,7,9,11,13,15)
AND page_is_redirect = 0
LIMIT 20;
*/
USE wikidatawiki_p;
SELECT r.rev_id, r.rev_timestamp, r.rev_len, rev.rev_len
FROM revision_userindex AS r
JOIN revision AS rev ON rev.rev_id = r.rev_parent_id
JOIN actor_revision ON actor_id = r.rev_actor
JOIN comment_revision ON comment_id = r.rev_comment_id
WHERE actor_name = 'MatSuBot' AND comment_text LIKE '%; cleanup'
AND rev.rev_len > r.rev_len
AND rev.rev_len - r.rev_len > 200
AND r.rev_timestamp > '20201020';
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...