reputation from upvotes, with a per-user 200 daily cap

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

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

Enter Parameters

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