Upvotes Over Time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Worldbuilding

Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

DECLARE @DayToScan DATE
SET @DayToScan = CAST('20 September 2014' AS DATE)

WHILE(DATEDIFF(dd, @DayToScan, GETDATE()) > 0)
BEGIN

DECLARE @LastPostID INT
SET @LastPostID = 0

DECLARE @TotalPosts DECIMAL
SET @TotalPosts = 0.0

DECLARE @TotalVotes DECIMAL
SET @TotalVotes = 0.0

DECLARE @VotesPerPost DECIMAL
SET @VotesPerPost = 0.0

DECLARE @PostIDToHandle INT
  
SELECT TOP 1 @PostIDToHandle = Id, @TotalVotes = @TotalVotes + Score
FROM Posts
WHERE Id > @LastPostID
      AND CONVERT(date, CreationDate) = @DayToScan
ORDER BY Id

WHILE @PostIDToHandle IS NOT NULL
BEGIN
    SET @TotalPosts = @TotalPosts + 1

    SET @LastPostID = @PostIDToHandle
    SET @PostIDToHandle = NULL

    SELECT TOP 1 @PostIDToHandle = Id, @TotalVotes = @TotalVotes + Score
    FROM Posts
    WHERE Id > @LastPostID
          AND CONVERT(date, CreationDate) = @DayToScan
    ORDER BY Id
END

SET @VotesPerPost = @TotalVotes / @TotalPosts

PRINT CONCAT(@DayToScan, ': ', 
             @TotalPosts, ' posts, ',
             @TotalVotes, ' votes: ',
             @VotesPerPost, ' votes per post.')

SET @DayToScan = DATEADD(wk, 1, @DayToScan)

END

Enter Parameters

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