nlWikiquote: All new articles the past few weeks

New  articles on Dutch WikiQuote from  the last few weeks.  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"

This takes of the order of 30s to 45s elapsed for a 4 week period so is quite expensive and has hard coded dates in the query, the two lower dates must be the same and the upper date is best set about a week or more higher than the current date.  This is an interim query.

SQL

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;

SQL

Checking query status...