Q&A for power users of web applications
WITH TopQuestions AS ( SELECT Id, Title, CreationDate, Score FROM Posts WHERE PostTypeId = 1 -- Question AND DATEDIFF(year, CreationDate, GETDATE()) <= 1 ORDER BY Score DESC, CreationDate DESC OFFSET 200000 ROWS FETCH NEXT 1 ROWS ONLY ), BestAnswers AS ( SELECT ParentId, MAX(Score) AS MaxScore FROM Posts WHERE PostTypeId = 2 -- Answer GROUP BY ParentId ), QuestionTags AS ( SELECT PT.Id, STRING_AGG(PT.Tag, '|') WITHIN GROUP (ORDER BY PT.Tag) AS Tags FROM ( SELECT Id, REPLACE(REPLACE(Tag, '<', ''), '>', '') AS Tag FROM Posts CROSS APPLY (SELECT value FROM STRING_SPLIT(Tags, '>')) AS T(Tag) ) PT GROUP BY PT.Id ) SELECT TQ.Id AS Id, TQ.Title AS Question, TQ.CreationDate AS QuestionDate, TQ.Score AS Ranking, P.Body AS Answer, QT.Tags AS Tags FROM TopQuestions TQ JOIN BestAnswers BA ON TQ.Id = BA.ParentId JOIN Posts P ON P.ParentId = BA.ParentId AND P.Score = BA.MaxScore JOIN QuestionTags QT ON TQ.Id = QT.Id ORDER BY TQ.Score DESC, TQ.CreationDate;