Talk:Top 200 flag images that should use vector graphics

SQL

-- 2> /dev/null; date; echo '
/* Most used files from categories
 * 
 * Author: [[User:Dispenser]]
 * License: Public domain
 * Run time: 2 min <SLOW_OK>
 */
SET @rownum=0;
SELECT CONCAT(
  "File:",REPLACE(gil_to,"_"," "),
  "|",(@rownum:=@rownum+1),". ",
  "Used ", gil_count," times."/* on ",gil_wikis," wikis."/*-*/
) AS ""
FROM (
  SELECT gil_to, COUNT(DISTINCT gil_wiki) AS gil_wikis, COUNT(*) AS gil_count
  FROM categorylinks
  JOIN page ON cl_from=page_id
  JOIN globalimagelinks ON page_namespace=6 AND page_title=gil_to
  WHERE cl_to IN ("Flag_images_that_should_use_vector_graphics")
  /* [[WP:NS]] Count only content namespaces - remove for old counts */
  AND gil_page_namespace_id IN (0, 14, 100, 108)
  GROUP BY gil_to ORDER BY gil_count DESC
  LIMIT 200
) AS subquery;
;-- ' | sql commonswiki_p > ~/public_html/temp/convert2svg_top_flags-commonswiki.log; date

This could be improved with a few extra tables and GROUP_CONCAT(CONCAT("<br/>", gil_wiki, ": ", gil_wiki_count) to get the old format that included the per wiki numbers. This was a lot of code—I opted for simplicity. Dispenser (talk) 03:59, 27 August 2014 (UTC)

I should mention this query will only count pages in "content" namespaces (select are Articles, Category, Portal, and Book pages). This avoids issues such as a WikiProject banner inflating counts due to talk page transclusions. —Dispenser (talk) 22:44, 1 October 2014 (UTC)

No idea what the query does, but it has not yet caught File:Flag of Kafr El-Sheikh Governorate.PNG + File:Flag_of_Kafr_El-Sheikh_Governorate.svg. –Be..anyone (talk) 05:51, 30 October 2014 (UTC)

@Be..anyone: It shouldn't – it catches files that should be converted to SVG (those tagged with {{Convert to SVG}}), not those that are already converted ({{Vector version available}}). SiBr₄ (talk) 08:44, 30 October 2014 (UTC)

@SiBr4: , bad case of DEnglish on my side, sorry. The PNG is still listed, but the SVG exists. Last update was 2014-08-17, the SVG was uploaded 2014-09-30. If the page is manually updated please ignore me, because I completely missed several points. –Be..anyone (talk) 09:45, 30 October 2014 (UTC)

Right. The list does not appear to have ever been regularly updated, though McZusatz and his/her bots McPot and McTopot did most Top 200 list updates since March. McZusatz, is there a reason the bots don't run anymore? SiBr₄ (talk) 10:50, 30 October 2014 (UTC)

I am quite busy with other things right now and my laptop was broken for two months. I can try to update the lists every 1-2 weeks. --McZusatz (talk) 03:35, 3 November 2014 (UTC)

A more stable sort between updates (stops the shuffling of items) is ORDER BY CEIL(LOG10(gil_count)) DESC, gil_to ASC —Dispenser (talk) 00:47, 8 May 2015 (UTC)