Q&A about the site for administrators, end users, developers and designers for Craft CMS
-- Fetch top 100 questions in the last 6 months by view count WITH TopQuestions AS ( SELECT TOP 100 p.Id, p.Title, p.ViewCount, p.CreationDate, p.Score, p.Tags, p.AnswerCount FROM Posts p WHERE p.PostTypeId = 1 AND p.CreationDate >= DATEADD(MONTH, -6, GETDATE()) ORDER BY p.ViewCount DESC ), TopAnswers AS ( SELECT p.ParentId, p.Id AS AnswerId, p.Score, p.Body, p.CreationDate, ROW_NUMBER() OVER(PARTITION BY p.ParentId ORDER BY p.Score DESC) AS rn FROM Posts p WHERE p.PostTypeId = 2 AND p.ParentId IN (SELECT Id FROM TopQuestions) ) SELECT q.Id AS QuestionId, q.Title, q.ViewCount, q.CreationDate AS QuestionCreationDate, q.Score AS QuestionScore, q.Tags, q.AnswerCount, a.AnswerId, a.Score AS AnswerScore, a.Body AS AnswerBody, a.CreationDate AS AnswerCreationDate FROM TopQuestions q LEFT JOIN TopAnswers a ON q.Id = a.ParentId AND a.rn <= 5 ORDER BY q.ViewCount DESC, a.Score DESC