Dead, or barely alive tags with criteria for Docs review eligibility

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select 
    sum(case when e."All" =1 then 1 else 0 end) as "Jon Skeet tags",
    sum(case when e."All" BETWEEN 1 and ##DeadTreshold?5## then 1 else 0 end) as "Effectively Dead tags",
    sum(case when e."All" BETWEEN ##DeadTreshold##+1 and ##AliveTreshold?10##  then 1 else 0 end) as "Barely alive tags",   
    sum(case when e."All" > ##AliveTreshold## then 1 else 0 end) as "Live tags"
from ( 
  select b.name,
      sum(case when b.class=1 then 1 else 0 end) as "Gold users",
      sum(case when b.class=2 and u.reputation> ##SilverLevel?5000## then 1 else 0 end) as "Silver users",
      sum(case when b.class=3 and u.reputation> ##BronzeLevel?10000## then 1 else 0 end) as "Bronze users",
      1 as "Jon Skeet",
      sum(case when b.class=1 OR (b.class=2 and u.reputation> ##SilverLevel##) OR (b.class=3 and u.reputation> ##BronzeLevel##) then 1 else 0 end)+1 as "All"
    
  from (select UserId, Name, min(class) class from Badges where tagbased='true' group by UserId, Name) b
  left join  Users u on u.Id=b.UserId
  group by b.name) e

Enter Parameters

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