declare @reptarget int set @reptarget = ##RepTarget## if...


Please login or register to vote for this query.

(click on this box to dismiss)

Internet of Things Meta

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

declare @reptarget int
set @reptarget = ##RepTarget##

if object_id('tempdb..#t') is not null drop table #t

create table #t (
owneruserid int,
rn int,
creationdate datetime,
bounty int,
immune int,
votes_capped int,
total as isnull(immune,0) + isnull(votes_capped,0),
primary key clustered(owneruserid, rn))

insert #t
  rn=row_number() over (partition by owneruserid order by creationdate),
  sum(bounty) bounty,
  sum(coalesce(accepted+bounty,accepted,bounty)) immune,
  (select min(a) from (
     select sum(rep) a
     union all
     select 200) x) as votes_capped
  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 = v.postid
inner join users u on = p.owneruserid
where v.votetypeid in (1,2,3,8,9)
  and p.communityowneddate is null
  and u.reputation >= 1000
  --and = 22656 -- to check just Jon Skeet
union all
select p2.owneruserid, v.creationdate, null, null, 2
from votes v
inner join posts p1 on = v.postid
  and v.votetypeid = 1 -- question accepted by asker
inner join posts p2 on = p1.parentid
inner join users u on = p2.owneruserid
where p2.communityowneddate is null
  and u.reputation >= 1000
  --and = 22656 -- to check just Jon Skeet
) j
group by owneruserid, creationdate

-- incidentally the below also shows the highest one day score, ever
select top 25 owneruserid as [User Link], *
from #t order by 7 desc -- to check temp table data.

Enter Parameters

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