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_actor_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)
),
relevant_edits AS (
  SELECT
    revactor_actor,
    COALESCE(COUNT(revactor_rev), 0) AS num_edits_to_wp_pages
  FROM revision_actor_temp
  INNER JOIN wikiproject_pages
    ON (revactor_page = wikiproject_pages)
  WHERE
    revactor_timestamp >= 20210701000000
  GROUP BY
    revactor_actor
)
SELECT
  wp_participant_user,
  num_edits_to_wp_pages
FROM participants_with_ids
LEFT JOIN relevant_edits
  ON (wp_participant_actor_id = revactor_actor)
SQL

Checking query status...
