Q&A about the site for sound designers
WITH Answers AS ( SELECT * FROM Posts WHERE PostTypeId = 2 -- Answers ) SELECT TOP ##NumberOfQuestions:int## Questions.Id AS [Q Id], Questions.Score AS [Q Score], Questions.Viewcount AS [Q Views], Questions.AnswerCount AS [Q Answers], Questions.FavoriteCount AS [Q Favorites], Questions.Tags AS [Q Tags], Questions.CreationDate AS [Q Date], AcceptedAnswer.CreationDate [A Date], 'site://a/' + CAST(FirstAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, FirstAnswer.CreationDate) AS NVARCHAR) AS [Minutes to First Ans], 'site://a/' + CAST(AcceptedAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, AcceptedAnswer.CreationDate) AS NVARCHAR) AS [Minutes to Accepted Ans] FROM Posts AS Questions LEFT OUTER JOIN Answers AS FirstAnswer ON FirstAnswer.ParentId = Questions.Id LEFT OUTER JOIN Answers AS AcceptedAnswer ON AcceptedAnswer.Id = Questions.AcceptedAnswerId WHERE Questions.PostTypeId = 1 -- Questions and Questions.Tags LIKE '%jmonkeyengine%' AND year(Questions.CreationDate) between 2014 and 2018 AND FirstAnswer.Id = ( SELECT TOP 1 Id FROM Answers WHERE Answers.ParentId = Questions.Id ORDER BY CreationDate ASC ) Order by Questions.Score desc ;