Commons talk:ISA Tool/Stats

Queries

Query 1

Unique participants to @ISA on Commons Year 2022 V2.2 : https://quarry.wmcloud.org/query/68311

SELECT actor_name, COUNT(*) AS count
FROM revision
JOIN comment_revision ON rev_comment_id=comment_id
JOIN page ON rev_page=page_id
JOIN actor_revision ON rev_actor=actor_id
JOIN change_tag ON rev_id=ct_rev_id
JOIN change_tag_def ON ct_tag_id=ctd_id
WHERE
  (ct_tag_id=563)
  AND rev_timestamp BETWEEN 20220101000000 AND 20221231999999
  AND comment_text RLIKE 'campaign[0-9]+@ISA'
GROUP BY rev_actor
ORDER BY count DESC

Query 2

Unique participants to @ISA on Commons Year 2020 V1.0 : https://quarry.wmcloud.org/query/68316

SELECT actor_name, COUNT(*) AS count
FROM revision
JOIN comment_revision ON rev_comment_id=comment_id
JOIN page ON rev_page=page_id
JOIN actor_revision ON rev_actor=actor_id
JOIN change_tag ON rev_id=ct_rev_id
JOIN change_tag_def ON ct_tag_id=ctd_id
WHERE
  (ct_tag_id=414)
  AND rev_timestamp BETWEEN 20200101000000 AND 20201231999999
  AND comment_text RLIKE 'campaign[0-9]+@ISA'
GROUP BY rev_actor
ORDER BY count DESC

Query 3

Unique participants to @ISA on Common All Years V1.0 and V2.2: https://quarry.wmcloud.org/query/68314

SELECT actor_name, COUNT(*) AS count
FROM revision
JOIN comment_revision ON rev_comment_id=comment_id
JOIN page ON rev_page=page_id
JOIN actor_revision ON rev_actor=actor_id
JOIN change_tag ON rev_id=ct_rev_id
JOIN change_tag_def ON ct_tag_id=ctd_id
WHERE
  (ct_tag_id=414 OR ct_tag_id=593)
  AND rev_timestamp BETWEEN 20180101000000 AND 20221231999999
  AND comment_text RLIKE 'campaign[0-9]+@ISA'
GROUP BY rev_actor
ORDER BY count DESC

Query 4

Unique participants to @ISA on Commons Sept-Dec 2022 Isa-dev [1.0] : https://quarry.wmcloud.org/query/68315

SELECT actor_name, COUNT(*) AS count
FROM revision
JOIN comment_revision ON rev_comment_id=comment_id
JOIN page ON rev_page=page_id
JOIN actor_revision ON rev_actor=actor_id
JOIN change_tag ON rev_id=ct_rev_id
JOIN change_tag_def ON ct_tag_id=ctd_id
WHERE
  (ct_tag_id=593)
  AND rev_timestamp BETWEEN 20220901000000 AND 20221231999999
  AND comment_text RLIKE 'campaign[0-9]+@ISA'
GROUP BY rev_actor
ORDER BY count DESC