Q & A count by tag for the last 1 year

0

Please login or register to vote for this query.

(click on this box to dismiss)

tags parameter must be supplied as comma-separated list of tags, each in single quotes, for example: 'angularjs', 'vue.js', 'reactjs', 'jquery'

Computer Science Educators

Q&A for those involved in the field of teaching Computer Science

Declare @startDate AS Date = '12/07/2019'
Declare @endDate AS Date = '12/31/2020'

create table #tagQuestions  (
  Id int,
  TagId int,
  TagName nvarchar(34),
  AnswerCount int,
  Accepted int,
  ViewCount bigint,
  QuestionScore bigint,
  AnswerScore bigint,
  OwnerUserId int
)

insert #tagQuestions (
  Id,
  TagId, 
  TagName, 
  AnswerCount, 
  AnswerScore,
  Accepted, 
  ViewCount, 
  QuestionScore,
  OwnerUserId
) select
  q.Id as Id,
  t.Id as TagId,
  t.TagName as TagName,
  (select count(*) from Posts a where a.ParentId = q.Id) as AnswerCount,
  (select sum(Score) from Posts a where a.ParentId = q.Id) as AnswerScore,
  case when q.AcceptedAnswerId is null then 0 else 1 end as Accepted,
  q.ViewCount as ViewCount,
  q.Score as QuestionScore,
  q.OwnerUserId as OwnerUserId
from Posts q 
   inner join PostTags pt on q.Id = pt.PostId
   inner join Tags t on pt.TagId = t.Id
where q.ParentId is null and 
t.TagName in (##tags##) and
q.CreationDate  BETWEEN 
@startDate AND 
@endDate
 

create table #tags (
  TagName nvarchar(34), 
  Questions int,
  Answers int,
  Answered int, 
  Accepted int, 
  MultipleAnswered int,
  ViewCount bigint,
  QuestionScore bigint,
  AnswerScore bigint,
  QuestionDistinctAuthors int,
  QuestionAuthorsRep bigint
)

insert #tags (
  TagName, 
  Questions,
  Answers,
  Answered, 
  Accepted, 
  MultipleAnswered,
  ViewCount,
  QuestionScore,
  AnswerScore,
  QuestionDistinctAuthors
) select
  TagName as TagName,
  count(*) as Questions,
  sum(AnswerCount) as Answers,
  sum(case when AnswerCount > 0 then 1 else 0 end) as Answered,
  sum(Accepted) as Accepted,
  sum(case when AnswerCount > 1 then 1 else 0 end) as MultipleAnswered,
  sum(ViewCount) as ViewCount,
  sum(QuestionScore) as QuestionScore,
  sum(AnswerScore) as AnswerScore,
  count(distinct OwnerUserId) as DistinctAuthors
from #tagQuestions tq
group by TagName
  
update #tags set 
QuestionAuthorsRep = (
  select sum(Reputation) from Users 
    where id in (select OwnerUserId from #tagQuestions 
          where #tagQuestions.TagName = #tags.TagName
))


select TagName, 
       Questions,
       Answered * 100 / Questions as '% Answered',
       Accepted * 100 / Questions as '% Accepted'
from #tags

Enter Parameters

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