Answer times within a timeframe year over year for different tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

With SelectTag AS
     (SELECT PostTags.TagId as TagId, TagName, PostTags.PostId
     FROM PostTags INNER JOIN (SELECT * FROM Tags WHERE 
     TagName = '##Tag1##' or
     TagName = '##Tag2##' or
     TagName = '##Tag3##' or
     TagName = '##Tag4##' 
     ) as B on PostTags.TagId = B.Id),
     
     Answers AS 
     (SELECT * FROM PostTags INNER JOIN 
     (SELECT Posts.Id, ParentId, Posts.CreationDate, OwnerUserId
     FROM Posts WHERE PostTypeId = 2) as A on A.ParentId = PostTags.PostId),
     
     Diff AS (SELECT TaggedAnswer.Id, TaggedAnswer.ParentId as QuestionId, TaggedAnswer.TagName, Posts.CreationDate as QuestionDate, TaggedAnswer.CreationDate as AnswerDate
     FROM (SELECT Answers.Id, Answers.TagId, Answers.CreationDate, Answers.ParentId, SelectTag.TagName FROM Answers INNER JOIN SelectTag on SelectTag.PostId = Answers.ParentId) as TaggedAnswer 
     INNER JOIN Posts on TaggedAnswer.ParentId = Posts.Id WHERE Posts.PostTypeId = 1),

     New AS (SELECT DISTINCT Id, QuestionId, TagName, YEAR(QuestionDate) as Yr, Month(QuestionDate) as Mnth, DATEDIFF(mi, QuestionDate, AnswerDate) as TimeDifference FROM Diff),
     
     Source AS (SELECT QuestionId, TagName, Yr, Mnth, min(TimeDifference) as TimeDiff FROM New GROUP BY QuestionId, TagName, Yr, Mnth)
     
     SELECT TagName, Yr, Mnth, AVG(CAST(TimeDiff as bigint)) as AvgMins, Count(QuestionId) as NumberOfQuestions FROM Source WHERE TimeDiff <= ##Days## *24*60 GROUP BY TagName, Yr, Mnth;

Enter Parameters

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