Suggested Edit Editor Stats - past year

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

declare @AllEditors as int = (select count(distinct OwnerUserId) from SuggestedEdits where CreationDate > getdate()-365);

select OwnerUserId, count(*) Edits, count(RejectionDate) Rejected, count(ApprovalDate) Approved
into #EditorStats
from SuggestedEdits
where CreationDate > getdate()-365
group by OwnerUserId

select (case when Edits=1 then 1
  when Edits >= 1000 then 1000
  when Edits >= 500 then 500
  when Edits >= 100 then 100
  when Edits >= 10 then 10
  when Edits >= 2 then 2 end) "# Edits", 
  count(*) Users,
  Round(count(*)*100.0/@AllEditors,1) "% of all editors",
  Round(sum(Rejected)*100.0/sum(Edits),1) "% Rejected",
  Round(sum(Approved)*100.0/sum(Edits),1) "% Approved"
from #EditorStats
where OwnerUserId is not null
group by (case when Edits=1 then 1
  when Edits >= 1000 then 1000
  when Edits >= 500 then 500
  when Edits >= 100 then 100
  when Edits >= 10 then 10
  when Edits >= 2 then 2 end)
order by (case when Edits=1 then 1
  when Edits >= 1000 then 1000
  when Edits >= 500 then 500
  when Edits >= 100 then 100
  when Edits >= 10 then 10
  when Edits >= 2 then 2 end)
  
select Rejected "Anon rejected", Approved "Anon approved"
from #EditorStats
where OwnerUserId is null

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached