Leaderboard - Answer Reputation

0

Please login or register to vote for this query.

(click on this box to dismiss)

Top 'n' users within first 'n' days since account creation in Stack Exchange (default is top 10 users with accounts created in most recent 30 days; i.e., answer rank top new users) by reputation earned with answers (+upvotes, +accepts, +bounties, -downvotes) with at least 2 answers. The answer count, answer reputation rate, proportion of reputation from the top answer and proportion of reputation from bounties are also provided. One objective of this query is to monitor potential new contributors and interact with them (for example, incentivize to continue participating). Another objective is to learn about the overall rank reputation based on answers (just set the second parameter in which 'number of days' is equal to the site's age). Linked posts: MSE - https://meta.stackexchange.com/questions/197215/finding-potential-new-users-based-on-reputation-from-answers/323683#323683

Craft CMS Meta

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

DECLARE @TOP INT = ##TOP?10##;
-- TOP: TOP 'n' rank:

WITH order_top_answers AS -- for column 'Pct top answer rep'
(
 SELECT p.*,
        ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId ORDER BY p.Score DESC) AS [top_answer]
 FROM Posts p
 WHERE p.PostTypeId = 2 -- answers
       AND p.CommunityOwnedDate IS NULL
       AND p.OwnerUserId IS NOT NULL -- active accounts
)
SELECT TOP (@TOP) 
        u.Id AS [User Link], 
        sum(CASE v.VoteTypeId
            WHEN 1 THEN 15 -- accepted
            WHEN 2 THEN 10 -- up vote
            WHEN 3 THEN -2 -- down vote
            WHEN 9 THEN v.BountyAmount -- bounty earned
            ELSE 0
            END
           ) AS [Answer rep], -- rep from answers
        max(p.top_answer) AS [Count of answers], -- count of answers
        sum(CASE v.VoteTypeId
            WHEN 1 THEN 15
            WHEN 2 THEN 10
            WHEN 3 THEN -2
            WHEN 9 THEN v.BountyAmount
            ELSE 0
            END
            ) / max(p.top_answer) AS [Answer rep rate]
FROM order_top_answers p
INNER JOIN Users u ON u.Id = p.OwnerUserId
LEFT JOIN Votes v on v.PostId = p.Id
WHERE (v.VoteTypeId IN (1,2,3,9) 
       OR v.VoteTypeId IS NULL) -- so answers yet not voted can be counted on.
       AND DateDiff(day, p.CreationDate, v.CreationDate) < 8
       AND DateDiff(month, p.CreationDate, u.CreationDate) >= 2
       AND DateDiff(month, p.CreationDate, u.CreationDate) < 4
       AND year(u.CreationDate) = 2016
       OR  year(u.CreationDate) = 2017
-- days: First 'n' days from 'new users' in Stack Exchange: 
GROUP BY u.Id
HAVING max(p.top_answer) >= 10 -- at least 2 answers.
       AND sum(CASE v.VoteTypeId
               WHEN 1 THEN 15
               WHEN 2 THEN 10
               WHEN 3 THEN -2
               WHEN 9 THEN v.BountyAmount
               ELSE 0
               END
               ) > 10 -- this is to avoid division by zero in 'Pct' columns
ORDER BY [Answer rep] DESC, [Answer rep rate] DESC

Enter Parameters

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