See top suggested, approved, rejected editors

1

Please login or register to vote for this query.

(click on this box to dismiss)

Philosophy Meta

Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence

declare @threshold int = ##mineditsbest##;
declare @thresh2 int = ##mineditsworst##;

with editors as (
select 
  userid, userid as [User Link], count(*) as "total edits",
  sum(rej) as rejected, sum(app) as approved,
  sum(app) * 100 / count(*) as "percentage approved"
from
  (select owneruserid as userid, 
          case when rejectiondate is null then 0 else 1 end as rej,
          case when approvaldate is null then 0 else 1 end as app
          from suggestededits) a
group by userid
),

unsorted as (
select 'top rejectee' as type, *, 
       row_number() over(order by rejected desc) as rank 
  from (select top 20 *
        from editors order by rejected desc) a
union
select 'top approvee' as type, *,
       row_number() over(order by approved desc) as rank
  from (select top 20 * from editors order by approved desc) b
union
select 'top overall' as type, *,
       row_number() over (order by "total edits" desc) as rank
  from (select top 20 * from editors order by "total edits" desc) c
union
select 'best percentage' as type, *,
       row_number() over(order by "percentage approved" desc, 
                         approved desc) as rank
  from (select top 20 * 
        from editors 
        where "total edits" > @threshold 
        order by "percentage approved" desc, approved desc) d
union 
select 'worst percentage' as type, *,
       row_number() over (order by "percentage approved" asc) as rank
  from (select top 20 * 
        from editors 
        where "total edits" > @thresh2 
        order by "percentage approved" asc) e
)

select * from unsorted order by type, rank

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results