Fork of #SheSaid on English Wikiquote from Oct 1st 2022 till 31 Dec by Djm-leighpark
This query is marked as a draft This query has been published by Djm-leighpark.

SQL

AخA
 
SELECT
  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "New WikiQuote #SheSaid article",
  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",
  (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
                            AND    r3.rev_timestamp BETWEEN 20221001000000 AND 20221231999999
                           )
   ) AS "Length",
   NVL((SELECT "Yes"
        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 ('Vfd-new','Dated_prod','Delete','Db','Copyvio')
                     )
       ),"No") AS "Problem"
FROM
  revision r
    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
                                          JOIN comment_revision ON rev_comment_id=comment_id
                                          JOIN page ON rev_page=page_id
                                WHERE 
                                          rev_timestamp BETWEEN 20221001000000 AND 20221231999999
                                          AND comment_text RLIKE '.*#[Ss]he[Ss]aid.*'
                                          AND page_namespace = 0
                                          AND page_is_redirect = 0)
                  GROUP BY rev_page
                  HAVING MIN(rev_timestamp) >= 20221001000000)
ORDER BY page_title 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...