WikiProject Participation

For all usernames on a given page -- e.g., WikiProject participant list -- count how many edits each has made to articles in that WikiProject over some time period.

SQL

# all usernames linked to on the participants page
WITH participants AS (
  SELECT DISTINCT
    SUBSTRING_INDEX(pl_title, '/', 1) AS wp_participant_user  # sometimes users link to subpages -- this just captures their username    
  FROM pagelinks
  WHERE
    pl_from = 10034790 # https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Spoken_Wikipedia/Participants
    AND pl_namespace = 2  # User namespace
) SELECT
    wp_participant_user,
    actor_id AS wp_participant_user_id
  FROM participants
  INNER JOIN actor
    ON (wp_participant_user = actor_name)

SQL

Checking query status...