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.