3

Please login or register to vote for this query.

(click on this box to dismiss)

Question/Answer count, users, max and avg score for up to 3 tags

Q&A for professional and enthusiast programmers

-- Tag Statistics v2
-- Question/Answer count, users, max and avg score for a particular tag


declare @statstable table(
 tag varchar(20),
 type varchar(10),
 items int,
 posters int,
 acc int,
 good int,
 nice int,
 favsum int,
 maxscore int,
 avgscore float,
 maxviews int,
 avgviews float
)


set nocount on






insert into @statstable select 
  TagName,
  'questions', 
  count(*),
  count(distinct p.OwnerUserId),
  sum(case when p.AcceptedAnswerId is not Null then 1 else 0 end),
  sum(case when p.Score > 10 then 1 else 0 end),
  sum(case when p.Score > 25 then 1 else 0 end),
  sum(isNull(p.FavoriteCount,0)),
  max(Score),
  round(avg(Score + 0.0),3),
  max(ViewCount),
  round(avg(ViewCount + 0.0),3)
from Posts p
join PostTags pt on pt.PostId = p.Id
join Tags t on t.Id = pt.TagId 
where t.TagName in ( ##Tag1:string## ,##Tag2:string##,##Tag3:string##)
and p.ParentId IS null and p.CommunityOwnedDate IS null and p.ClosedDate IS null
GROUP BY t.TagName 

insert into @statstable
select TagName,
  'answers',
  count(*),
  count(distinct p1.OwnerUserId), 
  NULL,
  sum(case when p1.Score > 10 then 1 else 0 end),
  sum(case when p1.Score > 25 then 1 else 0 end),
  NULL,
  max(p1.Score),
  round(avg(p1.Score + 0.0),3),
  NULL,
  NULL
from Posts p
join Posts p1 on p1.ParentId = p.Id 
join PostTags pt on pt.PostId = p.Id
join Tags t on t.Id = pt.TagId 
where t.TagName in ( ##Tag1:string## ,##Tag2:string##,##Tag3:string##)
and p.ParentId IS null and p.CommunityOwnedDate IS null and p.ClosedDate IS null
GROUP BY t.TagName



set nocount off 

select * from @statstable ORDER BY tag, type desc;

fork query

created may 13 11 ogerard