Hi,
For the WLM in the Netherlands I want to make statistics from how many monuments somebody made a picture.
In principle the sql for this is simple:
SELECT img_user_text, COUNT(img_user) FROM image JOIN page ON img_name=page_title JOIN categorylinks AS clA ON page_id=clA.cl_from WHERE page_namespace=6 AND page_is_redirect=0 AND clA.cl_to='Images_from_Wiki_Loves_Monuments_2011_in_the_Netherlands' and img_timestamp > '20110831220000' and img_timestamp < '20110930220000' GROUP BY img_user_text ORDER BY COUNT(img_user_text) DESC;
However, the uploader selected here is the last uploader, not the first. Due to this, one of the uploaders in this list is 'rotatebot'.
I know the other uploaders are in a different table, but I don't see an easy way to build the query, certainly because there is not always something in that table.
Does anybody know a good way to solve this?
Part of the reason I was looking for this is because of our quantity price, but fortunately the number of pictures of rotatebot is low enough not to have effect on the top 3.
Regards,
Andre