WikiDaheim users 2018 (by first revision, by uploader)

Fork of WikiDaheim users 2018 (by first revision, by uploader) by Herzi Pinki

This query is marked as a draft This query has been published by Herzi Pinki.

Count the number of images uploaded by users to a defined category. The firstedit column seems to be representing the first login to commons, not the registration via sul somewhere else.

SQL

USE commonswiki_p;
#describe image;
#describe revision;

SELECT /* SLOW_OK */ DISTINCT actor_name AS username, date_format(user.user_registration, "%Y-%m-%d") as firstedit, COUNT(image.img_name) as img_count 
    FROM actor, revision, image, page, categorylinks, user
    WHERE page.page_id=categorylinks.cl_from
        AND image.img_name = page.page_title
        and revision.rev_page = page.page_id
        and revision.rev_parent_id = 0
        AND categorylinks.cl_to = "Media_from_WikiDaheim_2018_in_Austria/all" 
        and user.user_id = revision.rev_actor
        and user.user_id = actor.actor_id
    GROUP BY username 
    ORDER BY img_count DESC limit 300;

SQL

Checking query status...