1

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

Q&A for people interested in statistics, machine learning, data analysis, data mining, and data visualization

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
)
-- credits to user 'rene'; code adapted from
-- https://meta.stackexchange.com/questions/315239/return-reputation-from-answers-get-a-permalink-of-the-results-in-the-stack-exc/315240#315240
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],  
        ROUND(CAST(sum(CASE 
                       WHEN v.VoteTypeId = 1 AND p.top_answer = 1 THEN 15
                       WHEN v.VoteTypeId = 2 AND p.top_answer = 1 THEN 10
                       WHEN v.VoteTypeId = 3 AND p.top_answer = 1 THEN -2
                       WHEN v.VoteTypeId = 9 AND p.top_answer = 1 THEN v.BountyAmount
                       ELSE 0
                       END
                       ) AS float) / 
              CAST(sum(CASE v.VoteTypeId
                       WHEN 1 THEN 15
                       WHEN 2 THEN 10
                       WHEN 3 THEN -2
                       WHEN 9 THEN v.BountyAmount
                       ELSE 0
                       END
                       ) AS float) * 100, 1) AS [Pct top answer rep],
        ROUND(CAST(sum(CASE 
                       WHEN v.VoteTypeId = 9 THEN v.BountyAmount
                       ELSE 0
                       END
                       ) AS float) / 
              CAST(sum(CASE v.VoteTypeId
                       WHEN 1 THEN 15
                       WHEN 2 THEN 10
                       WHEN 3 THEN -2
                       WHEN 9 THEN v.BountyAmount
                       ELSE 0
                       END
                       ) AS float) * 100, 1) AS [Pct bounties rep]
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 u.CreationDate > GETDATE() - ##days:int?30## -- new users (first 'n' days in SE).
-- days: First 'n' days from 'new users' in Stack Exchange: 
GROUP BY u.Id
HAVING max(p.top_answer) >= 2 -- 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, [Pct top answer rep] ASC;

fork query

edited feb 12

created jan 18 Andre Silva