/* Newbie Commons Videos

 * Author: Dispenser

 */

USE commonswiki_p;

SELECT

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

  ROUND(img_size/1024/1024) AS MB,

  CONCAT(img_width, "x", img_height) AS "Size",

  TIME_FORMAT(SEC_TO_TIME(CONVERT(

    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata,

    "\"playtime_seconds\";d:", -1), "\"length\";d:", -1), ";", 1),

  INTEGER)),'%k:%i:%s') AS "Length",

  img_user_text,

  CONCAT(DATEDIFF(NOW(), user_registration), " days ago") AS Registered,

  LEFT(img_description,50)

FROM image

JOIN user ON user_id=img_user

WHERE (user_editcount<100 AND user_registration > NOW() - INTERVAL 1 MONTH)

AND img_media_type="VIDEO"

AND img_size > 2e6

ORDER BY img_timestamp DESC

LIMIT 20;

​