Page MenuHomePhabricator
Paste P8503

mediawiki_history query for edit counts of active editors
ActivePublic

Authored by nshahquinn-wmf on May 10 2019, 4:54 AM.
Tags
None
Referenced Files
F28997431: raw.txt
May 10 2019, 4:54 AM
Subscribers
None
with apr_active_eds as (
select
event_user_text,
count(*) as content_edits
from wmf.mediawiki_history mwh
inner join wmf_raw.mediawiki_project_namespace_map ns_map
on
mwh.wiki_db = ns_map.dbname and
mwh.page_namespace = ns_map.namespace
where
ns_map.snapshot = "2019-04" and
mwh.snapshot = "2019-04" and
mwh.event_entity = "revision" and
mwh.event_type = "create" and
mwh.event_timestamp between "2019-04" and "2019-05" and
mwh.event_user_id != 0 and
ns_map.namespace_is_content = 1
group by event_user_text
having content_edits >= 5
)
select
event_user_text,
count(*) as edit_count
from apr_active_eds aae
inner join wmf.mediawiki_history mwh
on aae.event_user_text = mwh.event_user_text
where
mwh.snapshot = "2019-04" and
mwh.event_entity = "revision" and
mwh.event_type = "create" and
not mwh.revision_is_deleted_by_page_deletion
group by mwh.event_user_text