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

SQL

AخA
 
-- 2> /dev/null; date; echo '
/* 
 *
 * Run time: 25 minutes <SLOW_OK>
 */
USE commonswiki_p;
SELECT
  CONCAT("[[:File:", REPLACE(img_name, "_", " "), "]]") AS "File",
  img_size AS "Size",
  COUNT(*),
  COUNT(DISTINCT gil_wiki) AS "wikis",
  ROUND(img_size/COUNT(DISTINCT gil_wiki),1) AS "Spread"
FROM image
JOIN globalimagelinks AS gil ON gil_to=img_name
WHERE gil_page_namespace_id = 0
AND image.img_size BETWEEN 10*1024 AND 1000*1024
/* Use the img_media_mime index */
AND img_media_type="BITMAP" AND img_major_mime="image" AND img_minor_mime="jpeg"
GROUP BY img_name
ORDER BY spread ASC
LIMIT 5000;
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.

Query status: complete

Executed on Thu, 18 Jun 2015 03:41:09 UTC.