WSC-UK previous years' participants

Fork of WLE-GE participants 2014-2018 not active in 2019 by Antanana

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

SQL

use commonswiki_p;

SELECT CONCAT("# {{#target:User_talk:", actor_name, "|commons.wikimedia.org}}") as line
FROM revision r_prev
join categorylinks on rev_page = cl_from
join actor on rev_actor = actor_id and actor_user is not null
WHERE rev_parent_id = 0
and cl_to IN (
   "Images_from_European_Science_Photo_Competition_2015_in_Ukraine",
   "Images_from_Science_Photo_Competition_2016_in_Ukraine",
   "Images_from_Wiki_Science_Competition_2017_in_Ukraine"
) and not exists (
  SELECT 1
	FROM revision
	join categorylinks on rev_page = cl_from
	WHERE 
                  rev_parent_id = 0 AND  
                  rev_actor = r_prev.rev_actor	and
                  cl_to IN ("Images_from_Wiki_Science_Competition_2019_in_Ukraine")  
)  
 GROUP BY actor_name

SQL

Checking query status...