0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #VotesPerDay ([User Link] int NOT NULL,
                           [Post Link] int NOT NULL,
                           [Vote day] int NOT NULL,
                           --[Post day] int,
                           [Upvotes] int NOT NULL)
INSERT INTO #VotesPerDay
SELECT
  Min(p.OwnerUserId) AS [User Link],
  v.PostId AS [Post Link],
  Convert(int, Convert(datetime, v.CreationDate, 102)) / 86400 AS [Vote day],
  --Convert(int, Convert(datetime, Min(p.CreationDate), 102)) / 86400 AS [Post day],
  Count(*) AS [Upvotes]
FROM Votes v
LEFT JOIN Posts p ON p.Id = v.PostId
WHERE v.VoteTypeId = 2 -- upvote
  AND p.OwnerUserId IS NOT NULL -- filter out posts by deleted users
GROUP BY v.PostId, v.CreationDate
GO

SELECT TOP 10
  [User Link],
  Sum(Upvotes) AS [Capped],
  Sum(Upvotes) AS [Total upvotes]
FROM #VotesPerDay
GROUP BY [User Link]
ORDER BY [Total upvotes] DESC

fork query

edited jun 14 12

created jun 14 12 Gilles