Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
#SheSaid on English Wikiquote from Oct 1st 2022 till 31 Dec
by
Djm-leighpark
This query is marked as a draft
This query has been published
by
Djm-leighpark
.
New #SheSaid WikiQuote 2022 campaign articles. on English WikiQuote from Oct 1st 2022 till 31 Dec 2022. This is optimised for download as a wikitable and is even better when changed to a sortable wikitable by changing class="wikitable" to class="wikitable sortable" The last length size (in the #SheSaid campaign period) was being calculated incorrectly prior to Sun, 04 Dec 2022 21:48:13 UTC, this should now be fixed. 26-December-2022: Article pages currently subject to notifications such as "Dated prod", "Vfd-new" are now marked as "Yes" in the "Problem" column. 01-Jan-2023 add 'Db' fix.
Toggle Highlighting
SQL
SELECT CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "New WikiQuote #SheSaid article", actor_name AS "Creator", CONCAT( MID(rev_timestamp,1,4),'-',MID(rev_timestamp,5,2),'-',MID(rev_timestamp,7,2),' ', MID(rev_timestamp,9,2),':',MID(rev_timestamp,11,2),':',MID(rev_timestamp,13,2)) AS "Date created", IF(ISNULL(pp_value),pp_value,CONCAT('[[:d:',pp_value,']]')) AS "Wikidata item", (SELECT r2.rev_len FROM revision r2 WHERE r2.rev_id = (SELECT MAX(r3.rev_id) FROM revision r3 WHERE r3.rev_page=page_id AND r3.rev_timestamp BETWEEN 20221001000000 AND 20221231999999 ) ) AS "Length", NVL((SELECT "Yes" WHERE EXISTS (SELECT 1 FROM templatelinks tm1 JOIN linktarget lt1 ON lt1.lt_id=tm1.tl_target_id WHERE tm1.tl_from=page_id AND tm1.tl_from_namespace=0 AND lt1.lt_title IN ('Vfd-new','Dated_prod','Delete','Db','Copyvio') ) ),"No") AS "Problem" FROM revision r JOIN page ON rev_page=page_id JOIN actor_revision ON rev_actor=actor_id LEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'wikibase_item' WHERE rev_id in (SELECT rev_id FROM revision WHERE rev_page in (SELECT rev_page FROM revision JOIN comment_revision ON rev_comment_id=comment_id JOIN page ON rev_page=page_id WHERE rev_timestamp BETWEEN 20221001000000 AND 20221231999999 AND comment_text RLIKE '.*#[Ss]he[Ss]aid.*' AND page_namespace = 0 AND page_is_redirect = 0) GROUP BY rev_page HAVING MIN(rev_timestamp) >= 20221001000000) ORDER BY page_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
.
Checking query status...