My Money For Jam, Faster

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

DECLARE @LatestDate DATETIME
SELECT @LatestDate = MAX(CreationDate) FROM Posts
DECLARE @IgnoreDays NUMERIC = 15
DECLARE @MinAgeDays NUMERIC = 60

SELECT TOP 100
  PostId [Post Link],
  1.0 * ([Passive Up Reputation] - [Passive Down Reputation]) / [Days Counted] [Passive Rep Per Day],
  [Passive Up Reputation] - [Passive Down Reputation] [Passive Rep],
  [Passive Up Reputation],
  [Passive Down Reputation],
  [Days Counted]
FROM (
  SELECT
    v.PostId,
    SUM(CASE WHEN v.VoteTypeId = 2 THEN
      CASE WHEN p.PostTypeId = 1 THEN 5 ELSE 10 END
      ELSE 0 END) [Passive Up Reputation],
    SUM(CASE WHEN v.VoteTypeId = 3 THEN 2 ELSE 0 END) [Passive Down Reputation],
    DATEDIFF(DAY, p.CreationDate, @LatestDate) - @IgnoreDays [Days Counted]
  FROM
    Votes v
    INNER JOIN Posts p ON p.Id = v.PostId
  WHERE
    v.VoteTypeId IN (2, 3)
    AND p.CommunityOwnedDate IS NULL
    AND DATEDIFF(DAY, p.CreationDate, @LatestDate) > @MinAgeDays
    AND DATEDIFF(DAY, p.CreationDate, v.CreationDate) > @IgnoreDays
    AND p.OwnerUserId = ##UserId##
  GROUP BY
    v.PostId, p.CreationDate
  ) x
ORDER BY
  [Passive Rep Per Day] DESC

Enter Parameters

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