enWikiquote: Experimental: Potential untagged #SheSaid since 20221201

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

This query is marked as a draft This query has been published by Djm-leighpark.

This experimental query is designed to help identify some women's articles that are not tagged by .#SheSaid.  It does this by looking at templates and categories related to women.  This will miss some women articles that do not have such a template or article, and therefore a further sweep may be required.  In general there may be a lapse of 2 or 3 days before the identifying templates/categories are added so this query may be less effective for new articles.

There is a chance that this query could inappropriately identify an article for #SheSaid tagging when such a tag is inappropriate.

There is a completely different approach that can be achieve by looking at the relevant gender identity property in Wikidata.

Updated 26DEC2022 to include problem column and to check also for [Aa]ctresses in the categories.  The lack of a Problem item does not mean the article might be later nominated to PROD or VfD for notability concerns, this is more likely if no associated quality Wikidata item can be identified. 01JAN23 added 'Db' fix.

SQL

SELECT
  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "Potential untagged #SheSaid articles",
  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 '20221201000000'
                            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 '20221201000000'
                                                        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) >= '20221201000000' /* Must match lower interval above1 */
                )
AND NOT 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.*')
AND 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 ('Women-stubXXXX')
            UNION ALL
            SELECT 1
            FROM    categorylinks cl1
            WHERE   cl1.cl_from = page_id
            AND     (    /* cl1.cl_to LIKE 'Women%'
                      OR */ cl1.cl_to LIKE '% women' )
           ) 
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...