Q&A for Tridion developers and administrators
MIN(main.QuestionDate) as period,
AVG(DATEDIFF(day, QuestionDate, AnswerDate)) as TimeToFirstAnswer
Votes.Id as 'VoteId',
Votes.CreationDate as 'VoteDate'
Questions.Id as 'Question',
Questions.CreationDate as 'QuestionDate',
Questions.ViewCount as 'ViewCount',
Answers.CreationDate as 'AnswerDate',
Answers.Id as 'AnswerId',
Answers.Score as 'AnswerScore',
ROW_NUMBER() OVER (PARTITION BY Questions.Id ORDER BY Answers.CreationDate ASC) as 'AnswerNum',
CONCAT('https://stackoverflow.com/questions/', Questions.Id) as 'Link',
Questions.Tags as 'Tags'
Posts as Questions
LEFT OUTER JOIN Posts as Answers ON (Answers.ParentId = Questions.Id)
WHERE Questions.Tags LIKE '%google-cloud-dataflow%'
) AS base
LEFT OUTER JOIN Votes as Votes ON AnswerId = Votes.PostId
Votes.VoteTypeId IN (1, 2, 5) /*Vote is accepted, upvoted, favorited*/
) AS main
WHERE DATEDIFF(day, main.AnswerDate, main.VoteDate) <= 30 /* Votes within 30 days of answer */
GROUP BY DATEPART(month, main.QuestionDate), DATEPART(year, main.QuestionDate)
ORDER BY period