enWikiquote: All new articles since 20221001

Fork of enWikiquote: All new articles since 20221001 by Djm-leighpark

This query is marked as a draft This query has been published by DeirgeDel.

New  articles. on English WikiQuote from  1-Ocotober-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 latest version leverages the current date through the NOW() function and is set to examines new articles from the previous 2 weeks.  On eqWQ for an interval of 14 days the on a hot run it its performant at circa 2 s elapsed, however tests indicate a likely step peformance dropoff and possible exponential increases thereafter if that interval is increased, and database load may be also be one of several variable factors of influence. Two lower dates must be the same and could be replaced by a 'YYYYMMDD000000' value if necessary.  The effect of changing the upper date is undefined. 

The length of the article at the time of the query snapshot has been corrected for updates on or after Sun, 04 Dec 2022 23:09:30 UTC.

The 'ọ' flag notes marked for #SheSaid in the 2022 campaign, 'C', 'D', 'P' ,'V' flags indicated associated page is in the Copyvio, speedy Delete, Proposed deleteion, or Votes for deletion process that might lead to deletion. (Introduced 26 December 2022 20:48 UTC)  The 'N' flag indicates in the Category "Articles with no corresponding English Wikipedia article"

SQL

SELECT
   CONCAT('"',
          NVL((SELECT 'V'
              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 = 'Vfd-new'
                           )),'-'),
          NVL((SELECT 'P'
               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 = 'Dated_prod'
                           )),'-'),
          NVL((SELECT 'D'
              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 ('Delete', 'Db')
                           )),'-'),
          NVL((SELECT 'C'
              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 = 'Copyvio'
                           )),'-'),         
          NVL((SELECT 'N'
               WHERE EXISTS (SELECT 1
            		 		 FROM    categorylinks cl1
            				 WHERE   cl1.cl_from = page_id
            				 AND     cl1.cl_to = 'Articles_with_no_corresponding_English_Wikipedia_article'
                            )),'-'),
          NVL((SELECT 'ọ' WHERE EXISTS(SELECT 1
                                       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 "Flags",
  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
                              /* For specific dates used e.g. rev_timestamp BETWEEN '20221101000000' AND '20221201000000' */
                            AND     rev_timestamp BETWEEN '20221001000000'
                            AND     DATE_FORMAT(NOW(),'%Y%m%d%H%i%s')
                            AND     page_namespace = 0
                            AND     page_is_redirect = 0
                           )
   ) 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 '20221001000000'
                                                        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) >= '20221001000000' /* 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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...