0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with P (id, cw, protected, closed, locked) as (
  select id,
    case when communityowneddate is not null then 1 else 0 end,
    isnull((select top 1 case when posthistorytypeid=19 then 1 end
    from posthistory h
    where h.postid=p.id and posthistorytypeid in(19,20)
    order by h.id desc), 0),
    case when closeddate is not null then 1 else 0 end,
    isnull((select top 1 case when posthistorytypeid=14 then 1 end
    from posthistory h
    where h.postid=p.id and posthistorytypeid in(14,15)
    order by h.id desc), 0)
  from posts p
  where posttypeid=1
)
select coalesce(c.targettagname,b.targettagname,a.targettagname,tagname) tagname,
  count(*) [all],
  sum((1-closed)*(1-locked))*1.0/count(*) [%answerable],
  sum((1-closed)*(1-locked)) answerable,
  sum(closed*(1-locked)) closed,
  sum(locked) locked,
  sum(cw) cw,
  sum(protected) protected
from P
join posttags on(P.id=posttags.postid)
join tags T on(T.id=posttags.tagid)
left join tagsynonyms a on(T.tagname=a.sourcetagname)
left join tagsynonyms b on(a.targettagname=b.sourcetagname)
left join tagsynonyms c on(b.targettagname=c.sourcetagname)
group by coalesce(c.targettagname,b.targettagname,a.targettagname,tagname)
order by sum((1-closed)*(1-locked))*1.0/count(*) asc

fork query

edited aug 22 15

created aug 22 15 Deduplicator