SQL
x
USE commonswiki_p;
set @cat:="Media_from_WikiDaheim_2020_in_Austria/all"; # reference category counted
set @timestamp:=20200616220000; # registration time threshold for new users
SELECT /* SLOW_OK */ DISTINCT actor_name AS user, COUNT(image.img_name) as img_count, user.user_registration
FROM actor, revision, image, page, categorylinks, user
WHERE page.page_id=categorylinks.cl_from
AND actor.actor_id = revision.rev_actor
AND image.img_name = page.page_title
and revision.rev_page = page.page_id
and revision.rev_parent_id = 0
AND categorylinks.cl_to = @cat
and user.user_id = actor.actor_user
AND user.user_registration > @timestamp
GROUP BY user
ORDER BY img_count DESC limit 300;
SELECT /* SLOW_OK */ COUNT(image.img_name) as "Overall image count for above users"
FROM actor, revision, image, page, categorylinks, user
WHERE page.page_id=categorylinks.cl_from
AND actor.actor_id = revision.rev_actor
AND image.img_name = page.page_title
and revision.rev_page = page.page_id
and revision.rev_parent_id = 0
AND categorylinks.cl_to = @cat
and user.user_id = actor.actor_user
AND user.user_registration > @timestamp;
SELECT /* SLOW_OK */ actor_name AS user, image.img_name
FROM actor, revision, image, page, categorylinks, user
WHERE page.page_id=categorylinks.cl_from
AND actor.actor_id = revision.rev_actor
AND image.img_name = page.page_title
and revision.rev_page = page.page_id
and revision.rev_parent_id = 0
AND categorylinks.cl_to = @cat
and user.user_id = actor.actor_user
AND user.user_registration > @timestamp
limit 500;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.