Talk:State of the Commons/2015

Defining "registered users"

@Guillaume (WMF): is there a reason that you're using the Wikistats definition of "registered user" (any user who has made 10 or more edits) rather than the common-language definition (any user who has created an account)? The common-language version is probably closest to what CC is looking for. If it's just easy availability of numbers, I'm happy to run queries for the common-language versions of those numbers.—Neil P. Quinn-WMF (talk) 00:40, 17 October 2015 (UTC)

Neil P. Quinn-WMF: It was purely because it was the only number I had. At first, I was looking for numbers for that "common-language" definition, but I couldn't find any source of data to get that number for all Wikipedias combined, so I settled for the closest match I could find on WikiStats. Feel free to replace the numbers if you have a way of getting them :) For Commons, it's enough to look at c:Special:Statistics, but I wanted to use the same definition as for Wikipedia. If we use "users with an account" as metric, then we can update it for Commons as well. Guillaume (WMF) (talk) 01:09, 17 October 2015 (UTC)

@Guillaume (WMF):: Okay, cool! Adding the queries and results here for reference. I'll update the numbers on the page as well.—Neil P. Quinn-WMF (talk) 00:18, 20 October 2015 (UTC)

Registered Wikipedia accounts

SELECT COUNT( DISTINCT lu_name )
FROM centralauth.localuser
WHERE 
	lu_wiki LIKE "%wiki" AND
	lu_wiki NOT IN ( 
		"commonswiki", "wikidatawiki", "metawiki", "loginwiki", 
		"mediawikiwiki", "sourceswiki", "specieswiki", "testwiki", 
		"test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
		"outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki" ) AND
	lu_wiki NOT LIKE "wikimania%"
;

+---------------------------+
| COUNT( DISTINCT lu_name ) |
+---------------------------+
|                  42116280 |
+---------------------------+
1 row in set (2 min 42.44 sec)

New Wikipedia accounts in last 3 months

This isn't exactly comparable to the number above, because it doesn't count users that registered on a non-Wikipedia wiki and then had their account attached to a Wikipedia for the first time during the three months in question. However, I expect that number is negligible.

SELECT COUNT( * )
FROM centralauth.globaluser
WHERE 
	gu_registration >= "20150601000000" AND
	gu_registration <  "20150901000000" AND
	gu_home_db LIKE "%wiki" AND
	gu_home_db NOT IN ( 
		"commonswiki", "wikidatawiki", "metawiki", "loginwiki", 
		"mediawikiwiki", "sourceswiki", "specieswiki", "testwiki", 
		"test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
		"outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki" ) AND
	gu_home_db NOT LIKE "wikimania%"
;

+------------+
| COUNT( * ) |
+------------+
|    1087269 |
+------------+
1 row in set (2 min 25.86 sec)

Registered Commons accounts

SELECT COUNT( DISTINCT lu_name )
FROM centralauth.localuser
WHERE lu_wiki = "commonswiki";

+---------------------------+
| COUNT( DISTINCT lu_name ) |
+---------------------------+
|                   5253988 |
+---------------------------+
1 row in set (2 min 55.01 sec)

SELECT COUNT( * )
FROM commonswiki.user;

+-------------+
|  COUNT( * ) |
+-------------+
|  5254013    |
+-------------+

New Commons accounts in the last 3 months

SELECT COUNT( * )
FROM user
WHERE 
user_registration >= "20150601000000" AND
user_registration <  "20150901000000";

+------------+
| COUNT( * ) |
+------------+
|     283890 |
+------------+
1 row in set (12.27 sec)