Commons:Picture of the Year/2017/MessageR2/Users

From Wikimedia Commons, the free media repository
Jump to navigation Jump to search

Users who voted in R1 but not in R2.

USE commonswiki_p;
 SELECT DISTINCT CONCAT ( "User talk:" , rev_user_text )
FROM revision
WHERE rev_comment LIKE "+1 POTY vote - eligible on%" # on rev comment, so we don't have to inner join "page" table
AND rev_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -180 DAY), '%Y%m%d%H%i%s')
AND rev_user_text NOT IN ( # voted in R2 yet
  							SELECT DISTINCT rc_user_text AS rev_user_text
							FROM recentchanges
							WHERE rc_title LIKE "Picture_of_the_Year/2017/R2/v/%"
						  )
AND rev_user_text NOT IN ("Soyunalien", "Zhengqch", "Hamim000000", "Enenek", "Levy Headbanger", "Nimil Graceson", "Viktor Pazman", "Sunkizzed", "Hamza Ali Shah", "Karakondzula", "DiplomatTesterMan", "Ashley Wang", "Trzbne") #not eligible 
ORDER BY rev_user_text ASC; #sort abc

Add pages

Pages added:

    Pages in this list