Page MenuHomePhabricator

Commons deleted pages data lake query

Authored By
mfossati
Jul 7 2023, 2:13 PM
Size
758 B
Referenced Files
None
Subscribers
None

Commons deleted pages data lake query

SELECT page_id, revision_id, revision_parent_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

File Metadata

Mime Type
text/plain; charset=utf-8
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
10917542
Default Alt Text
Commons deleted pages data lake query (758 B)

Event Timeline