Getting User Statistics on Wikimedia Commons

Querying Wikimedia Commons DB to get User Statistics

Wikimedia Commons is an online repository of free-use images, sounds, and other media files. It is a project of the Wikimedia Foundation.

If you are a Commons user or need to get statistics for a particular user, then you can query the commonswiki DB to get the results. In this post, I will introduce you to a few SQL queries to get various user statistics.

Get Count of File Uploads

You can check the number of file uploads by a user using the following query:

use commonswiki_p;
select
count(*)
from
logging_userindex
where
log_type = "upload"
and log_user =(
select
user_id
from
user
where
user_name = "Maskaravivek"
);

You can run the query on Quarry.

Get Count of Deleted file uploads

Commons has a strict deletion policy and the community can nominate a picture for deletion if your upload violates any of the policies.

You can use the following query to get the count of deleted file uploads.

select count(*) from commonswiki_p.filearchive_userindex where fa_user_text="Maskaravivek"

commonswiki_p.filearchive_userindex has an index on username and querying it is much faster than querying the original table ie. commonswiki_p.filearchive .

You can run the query on Quarry.

Get Count of Articles Using Images

The whole purpose of the Commons image repository is to let Wikipedia articles use the images.

The query below gives the count of articles using images from a particular user.

select
count(*) as articlesUsing
from
commonswiki_p.globalimagelinks
where
gil_to in (
select
log_title
from
commonswiki_p.logging_userindex
where
log_type = "upload"
and log_user =(
select
user_id
from
commonswiki_p.user
where
user_name = "Maskaravivek"
)
);

You can run the query on Quarry.

Get Count of Unique Images USed

The query below gives the count of articles using images from a particular user.

select
count(distinct gil_to) as uniqueUsed
from
commonswiki_p.globalimagelinks
where
gil_to in (
select
log_title
from
commonswiki_p.logging_userindex
where
log_type = "upload"
and log_user =(
select
user_id
from
commonswiki_p.user
where
user_name = "Maskaravivek"
)
);

You can run the query on Quarry.

Get Count of Images Edited by Someone else

The pictures that you upload to commons can be edited by some other user as well. The following query can be used to get the count of such images.

use commonswiki_p;
select
count(*)
from
revision
where
rev_page in (
select
log_page
from
logging_userindex
where
log_type = "upload"
and log_user =(
select
user_id
from
user
where
user_name = "Maskaravivek"
)
)
and rev_user !=(
select
user_id
from
user
where
user_name = "Maskaravivek"
)
group by
rev_page
having
count(*) > 1

You can try the query on Quarry.

Get Number of Thanks received

Users can express thanks to other users using Commons.

The following query can be used to get the count of the number of thanks received by a user.

use commonswiki_p;
select count(*) from logging_logindex where log_type="thanks" and log_title="Maskaravivek";

The Commons Android app shows these statistics in a very nice interface.

User Achievements

Make sure you give this post 50 claps and follow me if you enjoyed this post and want to see more!