Tags with most closed question (percentage)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

with myTags as
(
  select 
    t.TagName as Name,
    count(pt.TagId) as Total,
    (select count (p.Id) from Posts p
     inner join PostTags pt2 on pt2.TagId = t.Id
     where p.Id = pt2.PostId and p.ClosedDate is not null)
    as ClosedTotal
  from Tags t
  inner join PostTags pt on pt.TagId = t.Id
  group by t.TagName, t.Id
),
tmpTable as
(
 select
 (select count(*) from posts p where p.posttypeid = 1) as Total,
 (select count(*) from posts p
   where p.posttypeid = 1 and p.ClosedDate is not null) as ClosedTotal
)

--- beurk
select TagName, Total, Closed, [%] from (
  select
     'total questions' as TagName, Total, ClosedTotal as Closed,
     (100. * ClosedTotal / Total) as [%],
     1 as myOrder -- put it at the top
  from tmpTable
  union
  select top 500
    t.Name as TagName,
    t.Total as Total,
    t.ClosedTotal as Closed,
    (100. * t.closedTotal / t.Total) as [%],
    2 as myOrder
  from myTags t
  where ClosedTotal >= ##MinClosed:int?10##
  order by myOrder, [%] desc
) xyz -- random name

Enter Parameters

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