cs-598-kcc-project-query-1

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drones and Model Aircraft Meta

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

DECLARE @page INT = 13
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,
  AnswerCount,
  Score,
  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,
  NULL as AnswerCount,
  Score,
  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 (
      SELECT 
        Body 
      FROM 
        Posts 
      WHERE 
        Posts.Id = #QAC.Id
    )
    WHEN #QAC.T = 'C'
    THEN (
      SELECT 
        Text
      FROM 
        Comments
      WHERE
        Comments.Id = #QAC.Id
    )
  END as Content,
  #UserFeatures.[U1.SUM],
  #UserFeatures.[U1.1],
  #UserFeatures.[U1.2],
  Users.CreationDate as U2,
  #QAC.AnswerCount as O1,
  #QAC.Score 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,
  #QAC.T,
  'S' as S,
  'P' as D
FROM
  #QAC,
  #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;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results