Q&A for administrators, end users, developers and designers for Craft CMS
DECLARE @page INT = 0 DECLARE @pageSize INT = 50000 SELECT Posts.Id, CreationDate, OwnerUserId as UserId, CASE WHEN PostTypes.Name = 'Answer' THEN 'A' WHEN PostTypes.Name = 'Question' THEN 'Q' END as T, ParentId INTO #QAC FROM Posts, PostTypes WHERE Posts.PostTypeId = PostTypes.Id and PostTypes.Name IN ('Answer', 'Question') UNION ALL SELECT Id, CreationDate, UserId, 'C' as T, PostId as ParentId FROM Comments SELECT UserId, COUNT(*) as [U1.SUM], SUM(CASE WHEN T = 'A' THEN 1 ELSE 0 END) as [U1.1], SUM(CASE WHEN T = 'Q' THEN 1 ELSE 0 END) as [U1.2] INTO #UserFeatures FROM #QAC GROUP By UserId SELECT #QAC.CreationDate, CASE WHEN #QAC.T IN ('A', 'Q') THEN Posts.Body WHEN #QAC.T = 'C' THEN Comments.Text END as Content, #UserFeatures.[U1.SUM], #UserFeatures.[U1.1], #UserFeatures.[U1.2], Users.CreationDate as U2, Posts.AnswerCount as O1, CASE WHEN #QAC.T IN ('A', 'Q') THEN Posts.Score WHEN #QAC.T = 'C' THEN Comments.Score END as Y1, CASE WHEN #QAC.T = 'A' THEN CASE WHEN #QAC.Id IN ( SELECT AcceptedAnswerId FROM Posts WHERE Posts.Id = #QAC.ParentId ) THEN 1 ELSE 0 END END as Y2, Users.Reputation as [U.REP], #QAC.T, 'S' as S, 'S' as D FROM #QAC LEFT JOIN Posts ON Posts.Id = #QAC.Id LEFT JOIN Comments ON Comments.Id = #QAC.Id, #UserFeatures LEFT JOIN Users ON #UserFeatures.UserId = Users.Id WHERE #QAC.UserID = #UserFeatures.UserId ORDER BY CreationDate DESC OFFSET @page * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;