1

Please login or register to vote for this query.

(click on this box to dismiss)

Gives an overview of how helpful anonymous editors are.

Q&A for amateur radio enthusiasts

-- Temporary table for all non-pending anonymous edits.
declare @AnonEdits table (Id int, Approved bit, Improved bit)

insert @AnonEdits (Id, Approved, Improved)
select
  s.Id as Id,
  case when s.ApprovalDate is not null then 1 else 0 end as Approved,
  max(case when v.UserId = -1 then 1 else 0 end) as Improved -- voted on by Community?
from
  SuggestedEdits s, SuggestedEditVotes v
where
  s.Id = v.SuggestedEditId
  and s.OwnerUserId is null -- anonymous
  and (s.ApprovalDate is not null or s.RejectionDate is not null) -- not pending
group
  by s.Id, s.ApprovalDate -- welp

-- all them variables
declare @TotalEdits int, @ApprovedEdits int, @ImprovedEdits int, @RejectedEdits int, @ApprovedImproved int, @RejectedImproved int

-- select us some data
select
  @TotalEdits = count(*),
  @ApprovedEdits = sum(case when Approved = 1 then 1 else 0 end),
  @ImprovedEdits = sum(case when Improved = 1 then 1 else 0 end),
  @ApprovedImproved = sum(case when Approved = 1 and Improved = 1 then 1 else 0 end)
from @AnonEdits

-- calculate the rest
select
  @RejectedEdits = @TotalEdits - @ApprovedEdits,
  @RejectedImproved = @ImprovedEdits - @ApprovedImproved

-- display it
select
  @TotalEdits as 'Total anonymous edits',
  @ApprovedEdits as 'Approved edits',
  @RejectedEdits as 'Rejected edits',
  cast(@ImprovedEdits as varchar) + ' total (' + cast(@ApprovedImproved as varchar) + ' approved, ' + cast(@RejectedImproved as varchar) + ' rejected)' as 'Improved edits',
  @ApprovedEdits - @ApprovedImproved as 'Approved as-is',
  @RejectedEdits - @RejectedImproved as 'Rejected as-is'

fork query

edited feb 1 12

created feb 1 12 e