Don't touch my junk.

0

Please login or register to vote for this query.

(click on this box to dismiss)

スタック・オーバーフローMeta

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

with FirstDay as (
    select Convert(Date, min(CreationDate)) as FirstDate,
           'http://stackoverflow.com' as Site,
           'StackOverflow' as DName
    from [StackOverflow]..Users
), TopUsers as (
    select Id as TopUser, DisplayName
    from [StackOverflow]..Users
    where Reputation >= 10000
    
), RepDays as (
            --Approved suggested edits.
            select TopUser, 
                   Convert(Date, se.ApprovalDate) as tstamp,
                   sum(2) as rep
            from TopUsers inner join [StackOverflow]..SuggestedEdits se on TopUser = se.OwnerUserId
              and se.ApprovalDate is not null
            group by TopUser, Convert(Date, se.ApprovalDate)
              
         UNION ALL
      
            -- Up and Down votes on Qs and As
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   case when 
                   sum(
                   case when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        else 0
                   end)
                   > 200 then 200 else
                   sum(
                   case when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        else 0
                   end)
                   end
                   +
                   sum(
                   case when v.VoteTypeId = 1 then 15
                        when v.VoteTypeId = 9 then IsNull(v.BountyAmount, 0)
                        else 0
                   end) as rep
            from TopUsers
            inner join [StackOverflow]..Posts p on TopUser = p.OwnerUserId
              and p.PostTypeId in (1,2)
            inner join [StackOverflow]..Votes v on p.Id = v.PostId
              and v.VoteTypeId in (1,2,3,9)
            group by TopUser, Convert(Date, v.CreationDate)
              
         UNION ALL
         
            -- Bounties that were offered....
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   sum(-1 * v.BountyAmount) as rep
            from TopUsers inner join [StackOverflow]..Votes v on TopUser = v.UserId
              and v.VoteTypeId = 8
            group by TopUser, Convert(Date, v.CreationDate)
), RepCumulative as (
    select TopUser,
           tstamp,
           sum (sum(rep)) over (partition by TopUser order by tstamp) as CumRep
    from RepDays
    group by TopUser, tstamp
), CarryOver as (
    select TopUser, min(tstamp) as tstamp
    from RepCumulative
    where CumRep >= 10000
    group by TopUser
)
select Top 3
       Site as Site,
       DName as DName,
       u.DisplayName as UserName,
       u.Id as UserId,
       FirstDate as FirstDate,
       rc.tstamp as MadeIt,
       rc.CumRep as Reputation
from RepCumulative as rc
   inner join CarryOver co on
       rc.TopUser = co.TopUser
   and rc.tstamp = co.tstamp
   inner join [StackOverflow]..Users u on
       rc.TopUser = u.Id
   inner join FirstDay on 1 = 1
order by rc.tstamp

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results