GlobalUsage weekly stats

GlobalUsage weekly stats for selected images are stored in p_globalusage_weekly_p on sql-s2.

Useful queries

 * Last update: SELECT MAX(cl_timestamp) FROM categorylinks;
 * Number of images in a certain category: SELECT COUNT(*) FROM categorylinks JOIN categories ON cl_category = cat_id WHERE cl_timestamp = AND cat_name = 
 * Total usage for a certain category: SELECT SUM(st_count) FROM stats JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp JOIN categories ON cl_category = cat_id WHERE cl_timestamp = AND cat_name = 
 * Total use for a certain category over time: SELECT cl_timestamp AS timestamp,SUM(st_count) AS count FROM stats JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp WHERE cl_category =  GROUP BY cl_timestamp
 * Number of images for a certain category not in use: SELECT COUNT(*) FROM stats RIGHT JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp JOIN categories ON cl_category = cat_id WHERE cl_timestamp = AND cat_name = AND st_image IS NULL
 * Usage for all images in a certain category per wiki: SELECT st_wiki, SUM(st_count) AS c FROM stats JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp JOIN categories ON cl_category = cat_id WHERE cl_timestamp = AND cat_name = GROUP BY st_wiki;
 * When a category is renamed at Commons: REPLACE INTO categories (cat_id, cat_name) VALUES (, ' ')

Reporting
We are looking for somebody who wants to create nice web pages and reports from the data we collect. If you are interested please contact Bryan or Multichill. Also if you would like to contribute other statistics please contact us and we can have you added to the MMP

Contact

 * Bryan 