0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for active researchers, academics and students of physics

PRINT ##StartCreationDate:string## + ' to ' + ##EndCreationDate:string## 

DECLARE @QuestionPostTypeId INT
DECLARE @AnswerPostTypeId INT
DECLARE @PostBodyCreateHistoryTypeId INT
DECLARE @VoteAcceptedType INT
DECLARE @VoteUpType INT
DECLARE @VoteDownType INT
DECLARE @VoteFavType INT

SELECT @QuestionPostTypeId = Id FROM PostTypes WHERE Name = 'Question'
SELECT @AnswerPostTypeId = Id FROM PostTypes WHERE Name = 'Answer'
SELECT @PostBodyCreateHistoryTypeId = Id FROM PostHistoryTypes 
  WHERE Name = 'Initial Body'

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
SELECT @StartTime = CAST(##StartCreationDate:string## AS DATE)
SELECT @EndTime = CAST(##EndCreationDate:string## AS DATE) 
 
SELECT @VoteAcceptedType = Id FROM VoteTypes WHERE Name = 'AcceptedByOriginator'
SELECT @VoteUpType = Id FROM VoteTypes WHERE Name = 'UpMod'
SELECT @VoteDownType = Id FROM VoteTypes WHERE Name = 'DownMod'
SELECT @VoteFavType = Id FROM VoteTypes WHERE Name = 'Favorite'
  
-- Get the post history in the specified time interval
DECLARE @FilteredPostHistory TABLE(PostId INT, CreationDate DATETIME) 
INSERT INTO @FilteredPostHistory  
SELECT PostId, CreationDate FROM PostHistory 
  WHERE 
  CreationDate BETWEEN @StartTime AND @EndTime AND
    PostHistoryTypeId = @PostBodyCreateHistoryTypeId
    
-- Get the top users by reputation
-- Get the top users by reputation
DECLARE @Temp TABLE(Id INT) 
INSERT INTO @Temp 
  SELECT TOP ##TopNPercent## PERCENT Id 
    FROM Users 
    ORDER BY Reputation DESC

DECLARE @TopUsers TABLE(Id INT) 
IF ##ChooseExperts:INT?0## = 1
BEGIN
  INSERT INTO @TopUsers 
    SELECT Id FROM @Temp
END
ELSE  
BEGIN
  INSERT INTO @TopUsers 
    SELECT TOP ##TopNNonExpertPercent:INT?20## PERCENT Id FROM 
    (
    SELECT Id FROM Users WHERE Users.Reputation > ##MinReputation:INT?100##
    EXCEPT 
    SELECT Id FROM @Temp
    ) AS foo
    ORDER BY newid()
END
  
-- Get the answer posts 
DECLARE @AnswerPostIds TABLE(PostId INT, OwnerId INT, ViewCount INT)
INSERT INTO @AnswerPostIds 
SELECT Posts.Id, Posts.OwnerUserId, Posts.ViewCount FROM Posts 
  INNER JOIN @FilteredPostHistory 
    ON PostId = Id
  INNER JOIN @TopUsers AS TopUsers
    ON Posts.OwnerUserId = TopUsers.Id
  WHERE Posts.PostTypeId = @AnswerPostTypeId
  ORDER BY Posts.OwnerUserId
  
-- Get the votes for the answer posts
PRINT 'Up votes per user'
SELECT AP.OwnerId as OwnerId, COUNT(VoteTypeId) AS UpVotes FROM Votes
INNER JOIN @AnswerPostIds AS AP ON AP.PostId = Votes.PostId
WHERE CreationDate < @EndTime AND VoteTypeId = @VoteUpType
GROUP BY OwnerId

fork query

edited apr 29 14

created apr 29 14 Devendra D. Chavan