Fork of frWikiquote: All new articles the past few weeks by Anthere
This query is marked as a draft This query has been published by Afek91.

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 '20221101000000' AND '20221201000000' */
                                          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.

Checking query status...