[Wikidata-bugs] [Maniphest] [Changed Project Column] T177354: Metrics for SDoC: look at contributions

[chelsyx]

chelsyx moved this task from In progress to Needs review on the Discovery-Analysis (Current work) board. chelsyx added a comment.

The number of files uploaded by bots is 9,390,408 (22.04%), and the number of files uploaded by users is 33,222,828 (77.96%). The following table break down the counts by media type:

img_major_mime	user_group	n_files
application	user	927448
application	bot	273617
audio	user	12479
audio	bot	2206
image	user	32242778
image	bot	9113650
video	user	40133
video	bot	935

Query:

SELECT img_major_mime, user_group, COUNT(*) AS n_files
FROM (
-- Get active/inactive bots
SELECT ug_user AS user_id, ug_group AS user_group
FROM user_groups
WHERE ug_group = 'bot'
UNION
SELECT ufg_user AS user_id, ufg_group AS user_group
FROM user_former_groups
WHERE ufg_group = 'bot'
UNION
-- Get user ids with bot categories in their user pages
SELECT user.user_id, 'bot' AS user_group
FROM user INNER JOIN (
  -- all user page names with bot category
  SELECT REPLACE(page.page_title, '_', ' ') AS user_name
  FROM page INNER JOIN (
    -- page ids with bot categories 
    SELECT DISTINCT cl_from AS page_id
    FROM categorylinks
    WHERE cl_to REGEXP '_(bot_flag|bots)(_|$)'
      AND cl_type = 'page'
  ) AS bot_cat ON page.page_id=bot_cat.page_id
  WHERE page_namespace = 2
) AS bot_name ON user.user_name=bot_name.user_name
) AS bots RIGHT JOIN image ON bots.user_id = image.img_user
GROUP BY img_major_mime, user_group;

TASK DETAIL

https://phabricator.wikimedia.org/T177354

WORKBOARD

https://phabricator.wikimedia.org/project/board/1241/

EMAIL PREFERENCES

https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: chelsyx
Cc: Aklapper, mpopov, chelsyx, Abit, SandraF_WMF, Ramsey-WMF, Capt_Swing, debt, E1presidente, Jmmuguerza, GoranSMilovanovic, QZanden, EBjune, Acer, Avner, Gehel, FloNight, Susannaanas, Wikidata-bugs, PKM, Base, matthiasmullie, aude, Ricordisamoa, Fabrice_Florin, Raymond, Mbch331

_______________________________________________
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs