User:PierreSelim/Wikimania 2014/Wiki Loves Monuments 2013 query for Guinness Book

CONNECT commonswiki_p commonswiki.labsdb;
SELECT /* SLOW_OK */ page.page_title AS title,
  image.img_timestamp AS Timestamp,
  oldimage.oi_timestamp AS 'original_timestamp',
  image.img_user_text AS 'last_uploader',
  IF(ug_img.ug_group IS NULL, 0, 1) AS 'last_uploader_group_is_bot',
  oldimage.oi_user_text AS 'first_uploader',
  IF(ug_oi.ug_group IS NULL, 0, 1)  AS 'first_uploader_is_bot'
FROM image 
LEFT JOIN user_groups ug_img ON (ug_img.ug_user = image.img_user AND ug_img.ug_group = 'bot')
CROSS JOIN page ON image.img_name = page.page_title     
CROSS JOIN categorylinks ON page.page_id = categorylinks.cl_from    
LEFT JOIN oldimage ON image.img_name = oldimage.oi_name AND oldimage.oi_timestamp = (SELECT MIN(o.oi_timestamp) FROM oldimage o WHERE o.oi_name = image
LEFT JOIN user_groups ug_oi ON (ug_oi.ug_user = oldimage.oi_user AND ug_oi.ug_group = 'bot')
  categorylinks.cl_to = "Images_from_Wiki_Loves_Monuments_2013"
ORDER BY img_timestamp ASC;