Reply Tool usage/user [before May-2020]

Fork of Reply Tool usage/user (Phase 0-3 wikis) by PPelberg (WMF)
This query is marked as a draft This query has been published by PPelberg (WMF).
SQL

select actor_name, count(*) as total
from (
	
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from enwiki_p.change_tag ct
inner join enwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join enwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join enwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'

union all
	
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from arwiki_p.change_tag ct
inner join arwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join arwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join arwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'

union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from cawiki_p.change_tag ct
inner join cawiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join cawiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join cawiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from ckbwiki_p.change_tag ct
inner join ckbwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join ckbwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join ckbwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'
  
union all
	
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from cswiki_p.change_tag ct
inner join cswiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join cswiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join cswiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'

union all
	
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from huwiki_p.change_tag ct
inner join huwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join huwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join huwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from frwiki_p.change_tag ct
inner join frwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join frwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join frwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' 

union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from jawiki_p.change_tag ct
inner join jawiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join jawiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join jawiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' 
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from kawiki_p.change_tag ct
inner join kawiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join kawiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join kawiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' 
  
union all  
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from kowiki_p.change_tag ct
inner join kowiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join kowiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join kowiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' 
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from nlwiki_p.change_tag ct
inner join nlwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join nlwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join nlwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' 
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from srwiki_p.change_tag ct
inner join srwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join srwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join srwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' 
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from svwiki_p.change_tag ct
inner join svwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join svwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join svwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'   
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from viwiki_p.change_tag ct
inner join viwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join viwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join viwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331'  
  
union all
  
select ct.ct_id, ct.ct_rev_id, actor_user, actor_name
from zhwiki_p.change_tag ct
inner join zhwiki_p.revision_actor_temp rat
on ct.ct_rev_id = rat.revactor_rev
inner join zhwiki_p.actor act
on rat.revactor_actor = act.actor_id
inner join zhwiki_p.change_tag_def
on ct_tag_id = ctd_id
where ctd_name = 'discussiontools-reply'
and substring(revactor_timestamp,1,8) >= '20200331' and substring(revactor_timestamp,1,8) <= '20200731'  
  
  
  
  
  
) a
group by actor_name
order by count(*) desc
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
