I started off with the query pointed out by Martijn and ended up with this beauty
Be careful if you count rows when you are joining tables...
Do note that these results might be slightly off because downvotes are anonymous so those votes on answers are not subtracted. SEDE is update weekly (On Monday!?) so at best the data is off for a week.
;
with topusers as (select top 200
id
, datediff(d , creationdate, getdate()) as Daysmember
from users
order by reputation desc) ,
voting as (
SELECT
SUM(case votes.votetypeid
WHEN 1 THEN 15 -- accept
WHEN 2 THEN -- upvote
CASE posts.posttypeid
WHEN 1 THEN 5 -- upvote question
WHEN 2 THEN 10 -- upvote answer
END
WHEN 3 THEN -2 -- downvote
WHEN 9 THEN BountyAmount -- collected bounty
END) as Rep,
Votes.CreationDate AS CreationDate,
Posts.OwnerUserId
FROM Posts
INNER JOIN Votes
ON Votes.PostId = Posts.Id
INNER JOIN topusers t
on t.id = Posts.OwnerUserId
WHERE ( Posts.CommunityOwnedDate IS NULL )
GROUP BY Votes.CreationDate,
Posts.OwnerUserId
),
bounty as (
SELECT
SUM(-BountyAmount) as Rep, -- Bounty given
Votes.CreationDate AS CreationDate,
Votes.UserID as OwnerUserId
FROM Votes
INNER JOIN topusers t on t.id = Votes.UserID
WHERE votes.votetypeid = 8
GROUP BY Votes.CreationDate,
Votes.UserID
),
sugedit as ( -- suggested edits
SELECT
COUNT(*) * 2 as Rep
, cast(ApprovalDate as date) As CreationDate
, OwnerUserId
FROM SuggestedEdits
INNER JOIN topusers t on t.id = SuggestedEdits.OwnerUserId
WHERE ApprovalDate IS NOT NULL
GROUP BY cast(ApprovalDate as date)
, OwnerUserId
)
select userid as [User Link]
, count(*) as [Days of Rep Cap]
, min(daysmember) [Days a member]
from (
select creationdate
, userid
, sum(votesrep + bountyrep + sugedtrep) as rep
from (
select v.creationdate as creationdate
, v.owneruserid as userid
, v.rep as votesrep
, 0 as bountyrep
, 0 as sugedtrep
from voting v
union
select b.creationdate
, b.owneruserid
, 0
, b.rep
, 0
from bounty b
union
select s.creationdate
, s.owneruserid
, 0
, 0
, s.rep
from sugedit s
) as ingroup
group by creationdate,
userid
having sum(votesrep + bountyrep + sugedtrep)>200
) cntdays
inner join topusers tu on tu.id = userid
group by userid
order by count(*) desc
The usual suspects on Stack Overflow are in the top result:
User Link |Days of Rep Cap|Days a member
--------------------------------------------
Jon Skeet | 2062 | 2101
Marc Gravell | 1606 | 2098
Darin Dimitrov| 1558 | 2078
BalusC | 1526 | 1776
Hans Passant | 1444 | 2109
SLaks | 1255 | 2062
VonC | 1156 | 2114
CommonsWare | 1139 | 1854
Greg Hewgill | 1025 | 2148
Mark Byers | 904 | 1971
Explaination about this query
I based the functional requirements of this query on the FAQ item How does “Reputation” work?. I'll match each bullet with the construction in the query so the query becomes verifiable (as requested in the comments). The text in code is the explanation.
Votes and Posts are joined on Postid and Id
Userid is here 1 (Jeff Atwood on SO).
You gain reputation when:
- one of your questions is voted up/useful: +5
Sum(5) from Votes where Votes.VoteTypeId == 2 and Post.PostTypeId = 1 and Post.OwnerUserId = 1
- one of your answers is voted up/useful: +10
Sum(10) from Votes where Votes.VoteTypeId == 2 and Post.PostTypeId = 2 and Post.OwnerUserId = 1
- one of your answers becomes accepted: +15
Sum(15) from Votes where Votes.VoteTypeId == 1 and Post.PostTypeId = 1 and Post.OwnerUserId = 1
- you accept an answer written by someone else to one of your own questions: +2
un accounted for yet
- a downvote on one of your questions or answers is removed: +2
this is not available in SEDE
- you suggest an edit and it is accepted: +2 (up to a total of +1000 per user)
Sum(2) from SuggestedEdits where OwnerUserid = 1 and ApprovalDate is not null
- you remove a downvote from an answer: +1
this is not available in SEDE (voting is anonymous)
- one of your answers is awarded a bounty by the user offering the bounty: +full bounty amount
Sum(BountyAmount) from Votes where Votes.VoteTypeId == 9 and Post.PostTypeId = 2 and Post.OwnerUserId = 1
- one of your answers is awarded a bounty automatically: +1/2 of the bounty amount (see bounty FAQ for details)
Sum(BountyAmount) from Votes where Votes.VoteTypeId == 9 and Post.PostTypeId = 2 and Post.OwnerUserId = 1
- you associate accounts of two or more Stack Exchange network sites, and at least one of those accounts already has 200 or more reputation: +100 on each site (awarded a maximum of one time per site)
Not available in SEDE (there is no cross-site query possible)
You lose reputation when:
- one of your questions or answers is voted down/not useful: −2
Sum(5) from Votes where Votes.VoteTypeId == 3 and Post.OwnerUserId = 1
- a post where you had successfully suggested an edit has been deleted (reputation page shows the cause as "removed"): -2
this is not in SEDE
- you vote an answer down/not useful: −1
this is not in SEDE
- an upvote on one of your questions is removed: −5
this event is not in SEDE (but not needed if you're intersted in totals)
- an upvote on one of your answers is removed: −10
this event is not in SEDE (but not needed if you're intersted in totals)
- one of your accepted answers loses accepted status: −15
this event is not in SEDE (but not needed if you're intersted in totals)
- you unaccept an answer written by someone else to one of your own questions: -2
this event is not in SEDE (but not needed if you're intersted in totals)
- you place a bounty on a question: −full bounty amount
Sum(-BountyAmount) from Votes (unjoinded with Posts!) where Votes.UserId =1
- one of your posts receives 6 spam or "it is not welcome in our community" flags (formerly known as offensive flags): −100
this is not in SEDE
