/* Images uploaded from Facebook by Newbies in last year

 * 

 * Runtime: 15 minutes

 */

USE commonswiki_p;

SELECT

  CONCAT("[[:File:", REPLACE(img_name,"_"," "), "]]") AS Filename,

  DATE_FORMAT(img_timestamp, "%Y-%m-%d") AS Uploaded,

  CONCAT("[[User:", img_user_text, "]] (", user_editcount, " edits",

    ", reg: ", DATE_FORMAT(user_registration, "%Y-%m-%d"), ")"

  ) AS User_info

FROM image

JOIN user ON user_id=img_user

JOIN page ON page_namespace=6 AND page_title=img_name

LEFT JOIN categorylinks ON cl_from=page_id AND cl_to IN (

  /* Exclude these categories */

  "Flickr_images_reviewed_by_FlickreviewR"

)

WHERE img_metadata LIKE "%SpecialInstructions\";a:1:{i:0;s:__:\"FBMD%"

AND cl_from IS NULL

  /* New users only */

AND user_editcount < 100

AND user_registration >= DATE_FORMAT(img_timestamp - INTERVAL 14 DAY, "%Y%m%d%H%i%S")

  /* Upload cut off date */

AND img_timestamp >= DATE_FORMAT(NOW() - INTERVAL 12 MONTH, "%Y%m%d%H%i%S");