Cross-wiki uploads: statistics on survived images, with user age

Fork of Cross-wiki uploads: statistics on survived images, with usage by Nemo bis

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

SQL

USE commonswiki_p;
SELECT img_name,
  img_width*img_height/1000000 AS Mpx,
  img_minor_mime AS type,
  gil_wiki AS usedIn,
  gil_page_namespace AS usedInNs,
  (UNIX_TIMESTAMP(img_timestamp)-UNIX_TIMESTAMP(log_timestamp))/3600/24 AS userAgeDays
  -- , user_editcount
FROM image
JOIN imagelinks
ON img_name = il_to
AND il_from = 45961050 -- https://commons.wikimedia.org/?curid=45961050
LEFT JOIN globalimagelinks
ON img_name = gil_to
JOIN logging_userindex
ON img_user = log_user
AND log_action = 'newusers';
-- JOIN user
-- ON img_user = user_id;

SQL

Checking query status...