/* Newbie Deleted Audio/Video

 *

 * A modification of 

 *

 * Author: Dispenser

 * License: Public domain

 */

USE commonswiki_p;

SELECT

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

  ROUND(fa_size/1024/1024) AS "MB",

  IF(fa_width=0, "", CONCAT(fa_width, "x", fa_height)) AS "Size",

  TIME_FORMAT(SEC_TO_TIME(CONVERT(

    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(fa_metadata,

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

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

  fa_user_text AS "Uploader",

  IF(EXISTS (SELECT 1 FROM ipblocks_ipindex WHERE ipb_user=user_id AND 

    (ipb_expiry > NOW() OR ipb_expiry="infinity")), "Blocked", user_editcount

  ) AS "Edits",

  DATE_FORMAT(user_registration,   "%H:%i %e-%b") AS "Registered",

  DATE_FORMAT(fa_timestamp,        "%H:%i %e-%b") AS "Uploaded",

  DATE_FORMAT(fa_deleted_timestamp,"%H:%i %e-%b") AS "Deleted   ",

  LEFT(fa_description,50) AS "Description",

  fa_deleted_reason AS "Deletion reason"

FROM filearchive

JOIN user ON user_name=fa_user_text

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

AND fa_media_type IN ("VIDEO", "AUDIO")

AND fa_size > 1e6 /* 1 MB minimum */

ORDER BY fa_timestamp DESC

LIMIT 1000;

​