2

Please login or register to vote for this query.

(click on this box to dismiss)

Code Review has a problem: an unusually large number of intermediate-level users between 150 and 500 reputation points. This query attempts to bring attention to answers posted by intermediate-level users who have been seen within the last month. Also see "Welcome intermediate users by reviewing their questions": http://data.stackexchange.com/codereview/query/161036

Q&A for peer programmer code reviews

WITH UsersAnswersLatestVotes AS (
    SELECT OwnerUserId AS UserId
         , MAX(Votes.CreationDate) AS LatestVote
        FROM Posts
           INNER JOIN Votes
               ON Votes.PostId = Posts.Id
        WHERE
            Posts.PostTypeId = 2      -- Answers
            AND VoteTypeId IN (2, 3)  -- Upvote or Downvote
        GROUP BY Posts.OwnerUserId
)
SELECT Answers.Id AS [Post Link]
     , Questions.Tags AS [Tags]
     , Answers.Score AS [Answer Score]
     , Questions.AnswerCount - 1 AS [# Competing Answers]
     , Users.Reputation AS [User's Reputation]
    FROM Posts AS Answers
        INNER JOIN Users
            ON Users.Id = Answers.OwnerUserId
        INNER JOIN Posts AS Questions
            ON Questions.Id = Answers.ParentId
        LEFT OUTER JOIN UsersAnswersLatestVotes
            ON UsersAnswersLatestVotes.UserId = Users.Id
    WHERE Users.Id > 1
        AND Users.Reputation BETWEEN 150 AND 500
        AND Users.LastAccessDate > DATEADD(MONTH, -1, GETDATE())
        AND Answers.Score >= -1
        AND Answers.PostTypeId = 2 -- Answers
        AND Answers.CommunityOwnedDate IS NULL
        AND Questions.ClosedDate IS NULL
        AND Questions.CommunityOwnedDate IS NULL
    ORDER BY DATEDIFF(DAY, 0, LatestVote) / 5
           , Users.LastAccessDate DESC;
-- Sort results by the time of the latest upvote or downvote on any
-- of the answerer's answers.  This sets up a roughly circular queue:
-- after an answer near the top of the list has been voted on, the
-- answerer goes to the back of the queue, giving other users' answers
-- fresh visibility.
--
-- Round off the time of the latest vote to 5 days.  Within each batch,
-- prioritize users who have been seen recently.

fork query

edited jan 24 14

created jan 17 14 200_success