Fork of WikiDaheim new users 2024 (by first revision, by uploader) and sum by Herzi Pinki
This query is marked as a draft This query has been published by Herzi Pinki.

SQL

​x
 
USE commonswiki_p;
set @cat:="Media_from_WikiDaheim_2025_in_Austria/all"; # reference category counted
set @timestamp:=20241217230000; # 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
        image
        JOIN page AS page_img ON image.img_name = page_img.page_title
        JOIN categorylinks ON page_img.page_id = categorylinks.cl_from
        JOIN revision ON revision.rev_page = page_img.page_id
        JOIN actor ON actor.actor_id = revision.rev_actor
        JOIN user ON user.user_id = actor.actor_user
    WHERE
        categorylinks.cl_to = @cat
        and revision.rev_parent_id = 0
        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
        image
        JOIN page AS page_img ON image.img_name = page_img.page_title
        JOIN categorylinks ON page_img.page_id = categorylinks.cl_from
        JOIN revision ON revision.rev_page = page_img.page_id
        JOIN actor ON actor.actor_id = revision.rev_actor
        JOIN user ON user.user_id = actor.actor_user
    WHERE
        categorylinks.cl_to = @cat
        and revision.rev_parent_id = 0
        AND user.user_registration > @timestamp
;
​
SELECT /* SLOW_OK */ actor_name AS user, concat('[[:File:',image.img_name,']]') as file
    FROM
        image
        JOIN page AS page_img ON image.img_name = page_img.page_title
        JOIN categorylinks ON page_img.page_id = categorylinks.cl_from
        JOIN revision ON revision.rev_page = page_img.page_id
        JOIN actor ON actor.actor_id = revision.rev_actor
        JOIN user ON user.user_id = actor.actor_user
    WHERE
        categorylinks.cl_to = @cat
        and revision.rev_parent_id = 0
        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.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...