Welcome intermediate users by reviewing their questions

1

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 questions posted by intermediate-level users who have been seen within the last month. Also see "Welcome intermediate users by reviewing their answers": http://data.stackexchange.com/codereview/query/160489

Code Review

Q&A for peer programmer code reviews

WITH UsersQuestionsLatestVotes AS (
    SELECT OwnerUserId AS UserId
         , MAX(Votes.CreationDate) AS LatestVote
        FROM Posts
           INNER JOIN Votes
               ON Votes.PostId = Posts.Id
        WHERE
            Posts.PostTypeId = 1      -- Questions
            AND VoteTypeId IN (2, 3)  -- Upvote or Downvote
        GROUP BY Posts.OwnerUserId
)
SELECT Questions.Id AS [Post Link]
     , Questions.Tags AS [Tags]
     , Questions.Score AS [Question Score]
     , Questions.AnswerCount AS [# Answers]
     , Users.Reputation AS [User's Reputation]
    FROM Posts AS Questions
        INNER JOIN Users
            ON Users.Id = Questions.OwnerUserId
        LEFT OUTER JOIN UsersQuestionsLatestVotes
            ON UsersQuestionsLatestVotes.UserId = Users.Id
    WHERE Users.Id > 1
        AND Users.Reputation BETWEEN 150 AND 500
        AND Users.LastAccessDate > DATEADD(MONTH, -1, GETDATE())
        AND Questions.PostTypeId = 1   -- Questions
        AND Questions.Score >= -1
        AND Questions.CommunityOwnedDate IS NULL
        AND Questions.ClosedDate 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 questions.  This sets up a roughly circular queue:
-- after a question near the top of the list has been voted on, the
-- asker 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.

Enter Parameters

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