Q&A for scientists using computers to solve scientific problems
WITH TopQuestions AS ( SELECT Id, Title, CreationDate, Score FROM Posts WHERE PostTypeId = 1 -- Question AND DATEDIFF(year, CreationDate, GETDATE()) <= 5 ORDER BY Score DESC, CreationDate DESC OFFSET 130000 ROWS FETCH NEXT 100000 ROWS ONLY ), BestAnswers AS ( SELECT ParentId, MAX(Score) AS MaxScore FROM Posts WHERE PostTypeId = 2 -- Answer GROUP BY ParentId ) SELECT TQ.Id AS Id, TQ.Title AS Question, TQ.CreationDate AS QuestionDate, TQ.Score AS Ranking, P.Body AS Answer FROM TopQuestions TQ JOIN BestAnswers BA ON TQ.Id = BA.ParentId JOIN Posts P ON P.ParentId = BA.ParentId AND P.Score = BA.MaxScore ORDER BY TQ.Score DESC, TQ.CreationDate DESC;