On 26 October 2011 21:24, Andre Koopal <andre@molens.org> wrote:

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

_______________________________________________
Wiki Loves Monuments mailing list
WikiLovesMonuments@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments
http://www.wikilovesmonuments.eu