English WikiQuote: new articles the past 35 days

New  articles. on English WikiQuote from  Oct 1st 2022 till 31 Dec 2022 (#SheSaid WikiQuote 2022 campaign)  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 75s elapsed to run and is therefore expensive.  It is believed to be essentially accurate.  Its could probably be improved by refactoring/profiling optimizer hints ...

SQL

SELECT
  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "New articles in a period",
  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"
FROM
  revision
    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
                                          LEFT JOIN page ON rev_page=page_id
                                WHERE 
                                /* rev_timestamp BETWEEN CONCAT(TO_CHAR(SYSDATE-35,'YYYYMMDD'),'000000')
                                                  AND CONCAT(TO_CHAR(SYSDATE+1,'YYYYMMDD'),'000000') */
                                STR_TO_DATE(rev_timestamp,'%Y%m%d%h%i%d') BETWEEN SYSDATE-35 AND SYSDATE + 1                                               AND CONCAT(TO_CHAR(SYSDATE+1,'YYYYMMDD'),'000000') */
                                          AND page_namespace = 0
                                          AND page_is_redirect = 0
                               )
                  GROUP BY rev_page
                 HAVING MIN(STR_TO_DATE(rev_timestamp.'%Y%m%d%h%i%d')) >= SYSDATE-35
/* AND   actor_name='Djm-leighpark' */
ORDER BY rev_timestamp ASC;

SQL

Checking query status...