Untitled query #24031

SQL

# count unreviewed pages in the English Wikipedia NPP backlog, by day of creation
# distinguished by users' autoconfirmed status at the time of page creation
SET @prev =0;
SET @editnum = 0;
SELECT LEFT(created,8) AS created_yyyymmdd,
IF (tenthedits.arev_timestamp IS NULL, 0, # user does not have a tenth edit
  IF(user.user_registration IS NULL, 1,  # accounts with missing reg date (rare) appear to be at least several years old  
    (tenthedits.arev_timestamp <= created) AND
      (DATE_ADD(user.user_registration,INTERVAL 4 DAY) <= CAST(created AS DATETIME))))
  AS autoconfirmed,
  # NB: there are also manually 'confirmed' users, and separate limits for IPBE users, 
  # but we assume that this difference is negligible here
COUNT(*) AS unreviewed_count
FROM (
    SELECT ptrp_created AS created, ptrp_page_id AS pageid
    FROM pagetriage_page, pagetriage_page_tags, page
    WHERE pagetriage_page.ptrp_page_id = pagetriage_page_tags.ptrpt_page_id 
    AND page.page_id = pagetriage_page_tags.ptrpt_page_id
    AND ptrp_reviewed = 0 AND page_namespace = 0 AND page_is_redirect=0 AND ptrpt_tag_id = 13) AS backlog
INNER JOIN  # find the user who created the page:
revision
ON pageid = revision.rev_page
INNER JOIN 
user
ON revision.rev_user = user.user_id
LEFT JOIN  # find the tenth edit of the page creator,
    # "LEFT" in order to keep users with <10 edits, i.e. no row in 'tenthedits'
(SELECT arev_user, editnum, arev_timestamp FROM ( 
# Use 'poor man's windowing' while we wait for MariaDB to be upgraded to 10.2.:
    SELECT IF(@prev <> arev_user, @editnum:=1, @editnum:= @editnum +1) AS editnum, 
    arev_timestamp, @prev:= arev_user, arev_user FROM (
        SELECT ar_timestamp AS arev_timestamp, ar_user AS arev_user
        FROM archive
        WHERE ar_user > 0
        UNION
        SELECT rev_timestamp AS arev_timestamp, rev_user AS arev_user
        FROM revision
        WHERE rev_user > 0
        ORDER BY arev_user, arev_timestamp) AS ordered_data
    ORDER BY arev_user, arev_timestamp) AS numberedlist
HAVING editnum = 10) AS tenthedits
ON user.user_id = tenthedits.arev_user
WHERE revision.rev_parent_id = 0
GROUP BY created_yyyymmdd, autoconfirmed 
ORDER BY created_yyyymmdd, autoconfirmed;
SQL

Checking query status...
