Best of Code Review - Best Newcomer (answer) category

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
Best of Code Review 2016 - Best Newcomer (answer) category
http://meta.codereview.stackexchange.com/questions/7128/best-of-code-review-2016-best-newcomer-answer-category
*/
DECLARE @Year AS INT = ##Year:int?2016##

DECLARE @Answer INT = 2;
WITH FirstAnswers AS (
SELECT
  MIN(p.Id) AS [PostId],
  DATEPART(YEAR, MIN(p.CreationDate)) AS [YearCreated],
  p.OwnerUserId
  FROM Posts AS p
  WHERE p.PostTypeId = @Answer
  GROUP BY p.OwnerUserId
)

SELECT
  p.Id AS [Post Link],
  p.OwnerUserId AS [User Link],
  p.Score,
  p.CreationDate
FROM 
  Posts AS p
  LEFT JOIN FirstAnswers AS fa
    ON fa.OwnerUserId = p.OwnerUserId
WHERE
  DATEPART(YEAR, p.CreationDate) = @Year
  AND fa.YearCreated = @Year
  AND p.ClosedDate IS NULL
  AND p.PostTypeId = @Answer
GROUP BY
  p.Id,
  p.OwnerUserId,
  p.Score,
  p.CreationDate
ORDER BY 
  p.OwnerUserId ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results