Monthly streak for NPP reviewers (2023)

SQL

SELECT page_title, date_format(rev_timestamp, '%Y-%m-%d') as 'Creation date'
FROM pagetriage_page
JOIN page ON page_id = ptrp_page_id
JOIN revision ON rev_page = page_id
JOIN pagetriage_page_tags ON ptrpt_page_id = page_id AND ptrpt_tag_id = (
  SELECT ptrt_tag_id
  FROM pagetriage_tags
  WHERE ptrt_tag_name = 'reference' -- "No Citations" tag in PageTriage
)
WHERE rev_parent_id = 0 -- makes rev_timestamp be the article creation date
AND rev_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -3 MONTH), "%Y%m%d%H%i%s") -- don't draftify articles older than 90 days, per rules
AND ptrp_reviewed = 0 -- unreviewed articles
AND ptrpt_value = 0 -- article has a "No Citations" tag in PageTriage
AND page_namespace = 0 -- mainspace (no drafts)
AND page_is_redirect = 0
  AND NOT EXISTS ( -- has no external links
    SELECT el_id
    FROM externallinks
    WHERE el_from = page_id 
  )
  AND NOT EXISTS (
    SELECT pp_propname
    FROM page_props
    WHERE pp_propname = 'disambiguation'
      AND pp_page = page_id
  ) -- is not a disambiguation page
  AND page_title NOT LIKE 'List_of_%' -- is not a list
  -- TODO: and is not tagged for deletion such as BLPPROD (check the pagetriage_page_tag for deletion)
SQL

Checking query status...
