This query is marked as a draft This query has been published by Dispenser.

SQL

x
 
/* Newbie Deleted Audio/Video
 *
 * A modification of Newbie Commons Videos, but for deleted files.
 *
 * Author: Dispenser
 * License: Public domain
 */
USE commonswiki_p;
SELECT
  CONCAT("[[File:", REPLACE(fa_name, "_", " "), "]]") AS "File",
  ROUND(fa_size/1024/1024) AS "MB", /* Technically MiB */
  IF(fa_width=0, "", CONCAT(fa_width, "x", fa_height)) AS "Size", /*Dimensions*/
  /* Get playtime_seconds from serialized PHP, convert to time, and format it */
  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", /* Run time */
  /* MariaDB regex to remove formatting */
  REGEXP_REPLACE(fa_deleted_reason, "<[^<>]+>|\\[\\[(.*?\\||)|\\]\\]", "") AS "Deletion reason",
  CONCAT(fa_user_text, " [", user_editcount, "]") AS "Uploader",
  (SELECT REGEXP_REPLACE(ipb_reason, "<[^<>]+>|\\[\\[(.*?\\||)|\\]\\]", "")
    FROM  ipblocks_ipindex
    WHERE ipb_user=user_id AND (ipb_expiry > NOW() OR ipb_expiry="infinity")
    ORDER BY ipb_timestamp DESC
    LIMIT 1
  ) AS "Blocked__________________",
  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"
/* Now the actual query */
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 700;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...