WikiProject Participation

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
),
participants_with_ids AS (
  SELECT
    wp_participant_user,
    actor_id AS wp_participant_user_id
  FROM participants
  INNER JOIN actor
    ON (wp_participant_user = actor_name)
),
# all articles in the wikiproject
wikiproject_pages AS (
  SELECT
    pa_page_id AS wikiproject_pages
  FROM page_assessments
  INNER JOIN page_assessments_projects
    ON (pap_project_id = pa_project_id
        AND pap_project_title = 'Spoken Wikipedia')
  INNER JOIN page
    ON (pa_page_id = page_id
        AND page_namespace = 0)
)
SELECT
  wp_participant_user,
  COUNT(revactor_rev) AS num_edits_to_wp_pages
FROM revision_actor_temp
INNER JOIN participants_with_ids
  ON (revactor_actor = wp_participant_user_id)
LEFT JOIN wikiproject_pages
  ON (revactor_page = wikiproject_pages)
WHERE
  revactor_timestamp >= 20210701000000
SQL

Checking query status...
