0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for scholars and enthusiasts of literature

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.UserId as [U.ID],
  #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,
  'P' 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;

view current version

edited nov 29 20

created nov 29 20 KoreanwGlasses