SQL
AخA
SELECT /*Not good enought yes - needs page & reivions into the query below*/
(SELECT '#SheSaid' WHERE EXISTS(SELECT 'ọ'
FROM comment_revision
JOIN revision x ON x.rev_comment_id=comment_id
WHERE x.rev_page=page_id
AND comment_text RLIKE '.*#[Ss]he[Ss]aid.*')) AS "ọ",
CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "New articles in a period",
IF(ISNULL(pp_value),pp_value,CONCAT('[[:d:',pp_value,']]')) AS "Wikidata item",
actor_name AS "Creator",
CONCAT( MID(r.rev_timestamp,1,4),'-',MID(r.rev_timestamp,5,2),'-',MID(r.rev_timestamp,7,2),' ',
MID(r.rev_timestamp,9,2),':',MID(r.rev_timestamp,11,2),':',MID(r.rev_timestamp,13,2)) AS "Date created",
(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
)
) AS "Length"
FROM
revision r
JOIN page ON r.rev_page=page_id
JOIN actor_revision ON r.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
LEFT JOIN page ON rev_page=page_id
WHERE
/* For specific dates used e.g. rev_timestamp BETWEEN '20231016000000' AND '20231030000000' */
rev_timestamp BETWEEN CONCAT(DATE_FORMAT(NOW()-INTERVAL 14 DAY,'%Y%m%d'),'000000')
AND DATE_FORMAT(NOW(),'%Y%m%d%H%i%s')
AND page_namespace = 0
AND page_is_redirect = 0
)
GROUP BY rev_page
HAVING MIN(rev_timestamp) >= CONCAT(DATE_FORMAT(NOW()-INTERVAL 14 day,'%Y%m%d'),'000000') /* Must match lower interval above1 */
)
ORDER BY rev_timestamp ASC;
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.