1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

WITH Raw AS (
  SELECT
    1 AS VoteType,
    Posts.OwnerUserId AS UserId,
    Votes.CreationDate AS VoteDay,
    COUNT(*) * 15 AS Rep
  FROM Posts, Votes
  WHERE
    Votes.PostId = Posts.Id
    AND
    Votes.VoteTypeId IN (1, 9)
  GROUP BY Posts.OwnerUserId, Votes.CreationDate

  UNION

  SELECT
    2 AS VoteType,
    Posts.OwnerUserId AS UserId,
    Votes.CreationDate AS VoteDay,
    CASE WHEN COUNT(*) > 20 THEN 200 ELSE COUNT(*) * 10 END AS Rep
  FROM Posts, Votes
  WHERE
    Posts.Id = Votes.PostId
    AND
    Votes.VoteTypeId = 2
  GROUP BY Posts.OwnerUserId, Votes.CreationDate
  
  UNION
  
  SELECT
    9 AS VoteType,
    Posts.OwnerUserId AS UserId,
    Votes.CreationDate AS VoteDay,
    SUM(Votes.BountyAmount) AS Rep
  FROM Posts, Votes
  WHERE
    Votes.PostId = Posts.Id
    AND
    Votes.VoteTypeId = 9
  GROUP BY Posts.OwnerUserId, Votes.CreationDate

)
SELECT TOP 100
  UserId AS [User Link],
  VoteDay,
  SUM(Rep) AS Rep
FROM Raw
WHERE UserId IS NOT NULL
GROUP BY UserId, VoteDay
ORDER BY Rep DESC, VoteDay DESC, UserId

fork query

edited feb 21 20

created feb 21 20 peterh - PLEASE DELETE THIS ACCOUNT