Fastest to 10k


Please login or register to vote for this query.

(click on this box to dismiss)

Query to find the fastest users to reach 10K

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

-- Fastest to 10k
-- Query to find the fastest users to reach 10K

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 >= 10000 -- quick pre-filter
  --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 >= 10000 -- quick pre-filter
  --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.

with cte as (
  select *,
    first_date = creationdate,
    running_total = isnull(total,0),
    bounty_included = isnull(bounty,0)
  from #t
  where rn=1
  union all
  select t.*,
    cte.running_total + isnull(,0),
    cte.bounty_included + isnull(t.bounty,0)
  from cte
  join #t t on t.rn = cte.rn+1 and t.owneruserid = cte.owneruserid
  where cte.running_total < 10000
       [User Link] = owneruserid,
       [Days Taken] = datediff(d, first_date, creationdate),
       [First vote received] = convert(varchar,first_date,102),
       [10k reached] = convert(varchar,creationdate,102),
       [Bounty Included] = bounty_included
from cte
where running_total >= 10000
order by datediff(d, first_date, creationdate)
option (maxrecursion 32767)

drop table #t

Enter Parameters

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