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;