Percent closed per canonical tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drones and Model Aircraft Meta

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

with canon as (
  select tags.id, count, tags.tagname as otag,
    COALESCE(c.targettagname, b.targettagname, a.targettagname, tagname) as tagname
  from tags
  left join tagsynonyms as a on(a.sourcetagname = tagname)
  left join tagsynonyms as b on(b.sourcetagname = a.targettagname)
  left join tagsynonyms as c on(c.sourcetagname = b.targettagname)
  -- two left joins suffice on SO at the moment, doing three for luck
)

select tagname, count(*) as closed, max(canon.Count) as questions,
  CAST(count(*) as float)/max(canon.Count) as percentclosed
from posts p
left join posttags pt on p.id = pt.postid
left join canon on pt.tagid = canon.id
where posttypeid = 1 and closeddate is not null and canon.Count >= 50
group by tagname
order by CAST(count(*) as float)/max(canon.Count) desc

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results