0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

select top 200[owneruserid] as [User Link], owneruserid, creationdate,
  immune+ votes_capped -bounty TotalExcludingBounty
from (

select
  owneruserid,
  rn=row_number() over (partition by owneruserid order by creationdate),
  creationdate,
  isnull(sum(bounty),0) bounty,
  sum(coalesce(accepted+bounty,accepted,bounty)) immune,
  (select min(a) from (
     select sum(rep) a
     union all
     select 200) x) as votes_capped
from
(
select
  p.owneruserid,
  v.creationdate,
  case votetypeid
      when 1 then 15
      end accepted,
  case votetypeid
      when 8 then -bountyamount
      when 9 then bountyamount
      end bounty,
  case votetypeid
      when 2 then 10
      when 3 then -2
      else 0
      end rep
from votes v
inner join posts p on p.id = v.postid
inner join users u on u.id = p.owneruserid
where v.votetypeid in (1,2,3,8,9)
  and p.communityowneddate is null
  and u.reputation >= 1000
) x
group by owneruserid, creationdate
) y
order by immune+ votes_capped -bounty desc

fork query

created mar 29 11 anonymous