Images usage supported by WMFR (2016-2017)

Fork of Images with label supported by WMFR (2013-2014) by PierreSelim

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

SQL

USE commonswiki_p;

SELECT /* SLOW_OK */
  COUNT(page.page_title) AS total_usage,
  COUNT(DISTINCT page.page_title) AS images_used,
  COUNT(DISTINCT gil.gil_wiki) AS nb_wiki
FROM image
CROSS JOIN page ON image.img_name = page.page_title
CROSS JOIN categorylinks ON page.page_id = categorylinks.cl_from
CROSS JOIN globalimagelinks gil ON gil.gil_to = image.img_name
LEFT JOIN oldimage ON image.img_name = oldimage.oi_name AND oldimage.oi_timestamp = (SELECT MIN(o.oi_timestamp) FROM oldimage o WHERE o.oi_name = image.img_name)
WHERE
  categorylinks.cl_to = "Media_supported_by_Wikimedia_France"
  AND IF(oldimage.oi_timestamp IS NULL, img_timestamp, oldimage.oi_timestamp)  BETWEEN 20130701000000 AND 20140701000000

SQL

Checking query status...