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