Wikidata - recent changes table

SQL

use metawiki_p; 
SELECT CURRENT_DATE;
show columns from recentchanges;


#Number of users in recentchanges table of Wikidata
#should match active users in https://www.wikidata.org/wiki/Special:Statistics without bots
#add 20 to cover the list in rc_user

SELECT COUNT(DISTINCT(rc_user)) as activeaccounts
FROM recentchanges
WHERE rc_bot=0
AND rc_user not in (0, 609373, 4115, 35388, 24057, 889167, 13330, 428643, 4935, 2590051, 2019252, 201849, 1105067, 8317, 9623, 165567, 48124, 74904, 203574, 7150, 16841)
;



#Number of users active at Wikidata
#add 20 to cover the list in rc_user
#result from February 1, 2016: 14494+20=14514

SELECT COUNT(DISTINCT(rc_user)) as activeusers
FROM recentchanges
WHERE rc_bot=0
AND rc_namespace=0
AND rc_user <> 0
AND rc_user not in (609373, 4115, 35388, 24057, 889167, 13330, 428643, 4935, 2590051, 2019252, 201849, 1105067, 8317, 9623, 165567, 48124, 74904, 203574, 7150, 16841)
AND rc_comment NOT LIKE '/* clientsitelink%'   #exclude users in client wikis
;



#Number of users creating statements
#add 20 to cover the list in rc_user
#result from February 1, 2016: 437+20=457

SELECT COUNT(DISTINCT(rc_user)) as activecontributors
FROM recentchanges
WHERE rc_bot=0
AND rc_namespace=0
AND rc_user <> 0
AND rc_user not in (609373, 4115, 35388, 24057, 889167, 13330, 428643, 4935, 2590051, 2019252, 201849, 1105067, 8317, 9623, 165567, 48124, 74904, 203574, 7150, 16841)
AND rc_comment LIKE '/* wbcreateclaim%'
;



#sample data
SELECT * FROM recentchanges LIMIT 100;
SELECT MAX(rc_timestamp) FROM recentchanges;
SELECT MIN(rc_timestamp) FROM recentchanges;
SQL

Checking query status...
