5

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for aircraft pilots, mechanics, and enthusiasts

SELECT TOP 1000
    tmp.[Date] AS [Date],
    tmp.[User Link] AS [User Link],
    tmp.[Before Cap Vote] AS [Uncapped Votes],
    tmp.[Accept] AS [Accepts],
    tmp.[Bounty] AS [Bounties],
    (CASE WHEN tmp.[Before Cap Vote] > 200 THEN 200
    ELSE tmp.[Before Cap Vote] END) +
    tmp.[Accept] + tmp.[Bounty]
    AS [Total Rep Gain]
FROM

    (SELECT
        v.CreationDate AS [Date],
        p.OwnerUserId AS [User Link],
--        COUNT(v.Id) AS [Votes],
        Sum(CASE
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0
            WHEN v.VoteTypeId = 2 and p.PostTypeId = 1 THEN 5
            WHEN v.VoteTypeId = 2 and p.PostTypeId = 2 THEN 10
            WHEN v.VoteTypeId = 3 THEN -2
            ELSE 0 END
        ) AS [Before Cap Vote],
        Sum(CASE
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0
            WHEN v.VoteTypeId = 1 THEN 15
            ELSE 0 END
        ) AS [Accept],
        Sum(CASE
            WHEN v.VoteTypeId = 9 THEN BountyAmount
            ELSE 0 END
        ) AS [Bounty]
    FROM Votes v
        JOIN VoteTypes vt ON vt.Id = v.VoteTypeId
        JOIN Posts p ON p.Id = v.PostId
    WHERE
        p.OwnerUserId is not null
    GROUP BY
        v.CreationDate,
        p.OwnerUserId
    ) AS tmp
    
ORDER BY [Total Rep Gain] DESC

fork query

created apr 24 13 Mysticial