Paste P49529

Commons deleted pages data lake query
ActivePublic
Actions

Authored by mfossati on Jul 7 2023, 2:13 PM.

Tags

None

Referenced Files

	F37132212: Commons deleted pages data lake query
	Jul 7 2023, 2:14 PM

	F37132211: Commons deleted pages data lake query
	Jul 7 2023, 2:13 PM

Subscribers

None

	SELECT page_id, revision_id, page_title, c.comment_text
	FROM wmf.mediawiki_history mh
	LEFT JOIN wmf_raw.mediawiki_logging l ON mh.page_id = l.log_page
	LEFT JOIN wmf_raw.mediawiki_private_comment c ON l.log_comment_id = c.comment_id
	WHERE mh.snapshot = '2023-05' AND c.snapshot = '2023-05' AND l.snapshot = '2023-05'
	AND event_timestamp >= '2022-05-01' AND event_timestamp < '2023-06-01'
	AND event_entity = 'revision'
	AND event_type = 'create'
	AND log_type = 'delete'
	AND log_action = 'delete'
	AND page_namespace= 6
	AND page_is_redirect IS NULL
	AND page_is_deleted
	AND mh.wiki_db = 'commonswiki' AND c.wiki_db = 'commonswiki' AND l.wiki_db = 'commonswiki'
	AND SIZE(event_user_is_bot_by) <= 0
	AND SIZE(event_user_is_bot_by_historical) <= 0

Event Timeline

mfossati created this paste.Jul 7 2023, 2:13 PM

mfossati edited the content of this paste. (Show Details)

mfossati mentioned this in T340546: [XL] Analysis of deletion requests on Commons.Jul 7 2023, 2:52 PM