User:Fæ/SignificantReverts

Userlist BLP Overwrites Significant Reverts Flickrstreams of concern

Table of images with the most active multiple overwrites by different contributors.

#	Date	Image	Hist	Users	Tag	Usage
001	2019-05-27	Kit body alianza19h.png	0	ShadowBryan3, RBEditions, ShadowBryan3, RBEditions	Needs categories	004
002	2019-05-29	Visa requirements for Chinese citizens holding ordinary and ordinary passports for public affairs and Two-way and Exit & Entry permits.png	0	Heitordp, Overandaway, Whisper of the heart, Twofortnights	Map	001
003	2019-05-29	2019 European election in Germany - Results.svg	0	大诺史, Erinthecute, 大诺史, Erinthecute		004
004	2019-05-31	Map of British India.png	0	Hanguoshabi, [[User:\|User:]], Hanguoshabi, Roy17	Map	013
005	2019-06-01	Wikipedia-VideoWiki-Dengue fever.webm	0	Evolution and evolvability, [[User:\|User:]], Ian Furst, Evolution and evolvability		001
006	2019-06-01	Flag of the Second East Turkestan Republic.svg	0	Aerbaniya, NuclearVacuum, Aerbaniya, Havsjö	Flag	214
007	2019-06-01	Écusson Commando marine - Intervention.svg	0	Futurhit12, [[User:\|User:]], 大诺史, Futurhit12		000
008	2019-06-01	Visa requirements Armenian citizens.png	0	Heitordp, [[User:\|User:]], Overandaway, Twofortnights	Map	010
009	2019-06-02	JESC 2019 Map.svg	0	LexPro4, [[User:\|User:]], AxG, Szyign		015
010	2019-05-31	ESC 2020 Map.svg	1	Yoyo360, Ahmedo Semsurî, Dimsar01, Ahmedo Semsurî		022
011	2019-06-01	Indian General Election 2019.svg	1	RaviC, संजीव कुमार, Maswimelleu, RaviC	Map	027
012	2019-05-28	European Parliament election	2	RaviC, JDuggan101, Brythones, Mirrorme22	Map	004
013	2019-06-02	Map of 2019 European Parliament election in the United Kingdom.svg	2	JDuggan101, RaviC, MrPenguin21, JDuggan101	Map	007

Key: Stable Unknown/possible BLP image Categories needed Map See tag; Date—The date of upload of the most recent image, within the past week.; Hist—Logarithm of file version history length, a likely indicator of stability with "2" showing hundreds of uploads.; Users—The last 4 users who have overwritten each other within 30 days.; Tag—Likely content based on a check of image category names.; Current—Image page is marked with {{Current}} or has been protected.

Report completed: Mon, 03 Jun 2019 04:19 (7m 40.2s runtime).

SQL query to generate this table.

SELECT DISTINCT
	LEFT(img_timestamp,8) AS date,
	img_name,
	FLOOR(LOG10(COUNT(*))) AS logreps,
	CONCAT(
	img_user_text, ', ',
	oi1.oi_user_text, ', ',
	oi2.oi_user_text, ', ',
	oi3.oi_user_text) AS users,
	pr_level AS protection
FROM image
INNER JOIN oldimage_userindex oi1 ON oi1.oi_name=img_name AND oi1.oi_sha1!=img_sha1 AND oi1.oi_user!=img_user
INNER JOIN oldimage_userindex oi2 ON oi2.oi_name=img_name AND oi1.oi_sha1!=oi2.oi_sha1 AND oi2.oi_user!=oi1.oi_user AND oi1.oi_timestamp>oi2.oi_timestamp
INNER JOIN oldimage_userindex oi3 ON oi3.oi_name=img_name AND oi2.oi_sha1!=oi3.oi_sha1 AND oi3.oi_user!=oi2.oi_user AND oi2.oi_timestamp>oi3.oi_timestamp
JOIN page ON page_title = img_name AND page_namespace = 6
LEFT JOIN page_restrictions ON page_id = pr_page AND pr_type = 'move'
WHERE
	img_name NOT LIKE "%Test%"
	AND	img_timestamp> DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -8 DAY), "%Y%m%d%H%i%s")
	AND oi1.oi_timestamp> DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -15 DAY), "%Y%m%d%H%i%s")
	AND oi2.oi_timestamp> DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY), "%Y%m%d%H%i%s")
	AND oi3.oi_timestamp> DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY), "%Y%m%d%H%i%s")
	AND img_user_text NOT REGEXP "review|Bot"
	AND oi1.oi_user_text NOT REGEXP "review|Bot"
	AND oi2.oi_user_text NOT REGEXP "review|Bot"
	AND oi3.oi_user_text NOT REGEXP "review|Bot"
GROUP BY img_name
ORDER BY logreps;

----

SELECT
	COUNT(DISTINCT gil_wiki),
	COUNT(DISTINCT gil_page),
	COUNT(DISTINCT (IF(gil_page_namespace="", gil_page, NULL)))
FROM globalimagelinks
WHERE gil_to='Map_of_2019_European_Parliament_election_in_the_United_Kingdom.svg';