0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students and teachers of all martial arts

DECLARE @page INT = 0
DECLARE @pageSize INT = 10000

SELECT
  Posts.Id,
  CreationDate,
  CASE 
    WHEN PostTypes.Name = 'Answer' THEN 'A'
    WHEN PostTypes.Name = 'Question' THEN 'Q'
  END as T
INTO #QARows
FROM
  Posts,
  PostTypes
WHERE
  Posts.PostTypeId = PostTypes.Id and
  PostTypes.Name IN ('Answer', 'Question')
ORDER BY Id DESC
OFFSET 0 ROWS
FETCH NEXT (@page+1)*@pageSize ROWS ONLY;

SELECT
  Id, 
  CreationDate,
  'C' as T
INTO #CRows
FROM
  Comments 
WHERE
  CreationDate > (SELECT MIN(CreationDate) FROM #QARows);

SELECT 
  *
INTO #Rows
FROM
  (
    SELECT * FROM #QARows
    UNION ALL
    SELECT * FROM #CRows
  ) as rows
ORDER BY CreationDate DESC
OFFSET @page * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;


SELECT
  #Rows.CreationDate,
  COALESCE(Posts.Body, Comments.Text) as Content,
  COALESCE(Posts.OwnerUserId, Comments.UserId) as [U.ID],
  userFeatures.[U1.SUM],
  userFeatures.[U1.1],
  userFeatures.[U1.2],
  userFeatures.[U1.3],
  Users.CreationDate as U2,
  Posts.AnswerCount as O1,
  COALESCE(Posts.Score, Comments.Score) as Y1,
  CASE 
    WHEN #Rows.T = 'A'
    THEN 
      CASE 
        WHEN Posts.Id = parent.AcceptedAnswerId
        THEN 1 ELSE 0 
      END
  END as Y2,
  Users.Reputation as [U.REP],
  #Rows.T,
  'S' as S,
  'P' as D
FROM
  #Rows
  LEFT JOIN Posts
  ON #Rows.T IN ('Q', 'A') and Posts.Id = #Rows.Id
  LEFT JOIN Comments
  ON #Rows.T = 'C' and Comments.Id = #Rows.Id
  LEFT JOIN Posts parent
  ON Posts.ParentId = parent.Id,
  Users,
  (
    SELECT 
      UserId,
      COUNT(*) as [U1.SUM],
      SUM(CASE WHEN T='Q' THEN 1 ELSE 0 END) as [U1.1],
      SUM(CASE WHEN T='A' THEN 1 ELSE 0 END) as [U1.2],  
      SUM(CASE WHEN T='C' THEN 1 ELSE 0 END) as [U1.3]
    FROM 
      (
        SELECT
          Posts.OwnerUserId as UserId,
          CASE 
            WHEN PostTypes.Name = 'Answer' THEN 'A'
            WHEN PostTypes.Name = 'Question' THEN 'Q'
          END as T
        FROM
          Posts,
          PostTypes
        WHERE
          Posts.PostTypeId = PostTypes.Id and
          PostTypes.Name IN ('Answer', 'Question')
        UNION ALL
        SELECT
          UserId,
          'C' as T
        FROM
          Comments  
      ) as qac
    GROUP BY UserId
    HAVING
      UserId IN (
        SELECT DISTINCT
          COALESCE(OwnerUserId, UserId)
        FROM 
          #Rows
          LEFT JOIN Posts
          ON #Rows.T IN ('Q', 'A') and Posts.Id = #Rows.Id
          LEFT JOIN Comments
          ON #Rows.T = 'C' and Comments.Id = #Rows.Id
      )
  ) as userFeatures
WHERE
  (Users.Id = Posts.OwnerUserId or
  Users.Id = Comments.UserId) and
  (userFeatures.UserId = Posts.OwnerUserId or
  userFeatures.UserId = Comments.UserId)
ORDER BY CreationDate DESC;

fork query

edited nov 29

created nov 29 KoreanwGlasses