enWikiquote: #SheSaid tagged updated articles (01-Oct-22 : 31-Dec-22)

Fork of enWikiquote: #SheSaid tagged updated articles (01-Oct-22 : 31-Dec-22) by Djm-leighpark

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

This is related to a query for new #SheSaid WikiQuote 2022 campaign articles. on English WikiQuote from  Oct 1st 2022 till 31 Dec 2022 and lists individual articles that identify as having been modified in the campaign period.  This query looks at mainspaces article only and precisely considers the articles that have a #SheSaid in the summary in the relevant period period and excludes articles newly created in the period. The query includes the metric showing an article size increase from its size at the start of the campaign, though this metric does not relate to article quality which is ultimately far more important. Identifying any individual author from this query would be problematic.   individal article inspections.  The query is 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 query has not been fully tested and there remains a risk it has inaccuracies.  It currently is not expensive to run with the elapsed time the order of a couple of seconds

The concerns of article size and increase raised prior to Sun, 04 Dec 2022 18:36:22 UTC  have been checked and found not to have applied to this query but an issue that would have occurred after 31-December-2022 has now been corrected so the length referred to will statically remain that of the last change in the campaign period

SQL

SELECT
  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "WikiQuote updated article in #SheSaid 2022",
  rvmax.rev_len - NVL(rvprior.rev_len,0) "Campaign size increase",
  rvmax.rev_len "Current size",
    CONCAT( MID(rvmax.rev_timestamp,1,4),'-',MID(rvmax.rev_timestamp,5,2),'-',MID(rvmax.rev_timestamp,7,2),' ',
            MID(rvmax.rev_timestamp,9,2),':',MID(rvmax.rev_timestamp,11,2),':',MID(rvmax.rev_timestamp,13,2)) AS "Last update",
  IF(ISNULL(pp_value),pp_value,CONCAT('[[:d:',pp_value,']]')) AS "Wikidata item"
FROM
  revision rvf
    JOIN page ON rvf.rev_page=page_id
    JOIN revision rvmax ON rvf.rev_page=rvmax.rev_page
    JOIN revision rvprior ON rvf.rev_page=rvprior.rev_page
    LEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'wikibase_item'
WHERE rvf.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)
AND rvmax.rev_id = (SELECT MAX(rev_id)
                    FROM   revision 
                    WHERE  rev_page=rvf.rev_page
                    AND    rev_timestamp < 20221231999999)
AND rvprior.rev_id = (SELECT MAX(rev_id)
                      FROM   revision 
                      WHERE  rev_page=rvf.rev_page
                      AND    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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...